Simple mysql query with 16 + seconds

Question

A simple sql query takes 16 seconds plus. Here is a table.

CREATE TABLE IF NOT EXISTS `udr` (
  `userid` int(11) NOT NULL DEFAULT '0',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `upbytes` int(11) NOT NULL DEFAULT '0',
  `downbytes` int(11) NOT NULL DEFAULT '0',
  `traffictype` int(11) NOT NULL DEFAULT '1',
  KEY `userid` (`userid`),
  KEY `time` (`time`),
  KEY `traffictype` (`traffictype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There are 16 million entries. (Originally there were 141 million, I cropped to see if this was a problem)

The query in question (for example)

select time,upbytes,downbytes
 from udr
  where userid = 315533 and 
        time between '2014-01-01 14:35:28' and '2014-01-02 14:35:28'

I thought the problem was a temporary part, so I removed the temporary condition and tried to execute

select time,upbytes,downbytes from udr where userid = 315533
...
10282 rows in set (19.42 sec)

The request still takes 16 seconds plus.

Here are my key configuration options

key_buffer    = 32M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size       = 8
query_cache_limit = 1M
query_cache_size        = 16M

ideas about it ended.

thanks

mysql> explain select * from udr where userid = '315533';
+----+-------------+-------+------+---------------+--------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows  | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+-------+-------+
|  1 | SIMPLE      | udr   | ref  | userid        | userid | 4       | const | 12738 |       |
+----+-------------+-------+------+---------------+--------+---------+-------+-------+-------+
1 row in set (0.02 sec)

iotop reports io heavy disk. I suspect mysql is retrieving all records in memory.

Answer 1

SELECT time,upbytes,downbytes
    FROM udr
    WHERE userid = 315533 AND
    time BETWEEN '2014-01-01 14:35:28' AND '2014-01-02 14:35:28'

, time userid. , MySQL .

MySQL time -index, userid. userid, 12738 , . MySQL , time -column.

, :

ALTER TABLE udr ADD KEY (`userid`, `time`);

MySQL userid, time .

. . .

Answer 2

time userid. , phpmyadmin sqlyog.

Answer 3

But a request with only a user ID still took almost the same amount of time.

You cannot do more. What are your hardware specifications? My settings are medium and taller than yours:

query_cache_size =  64 MiB
key_buffer =  128 MiB
max_allowed_packet  =  16M
thread_stack    =  294 Kb
thread_cache_size       = 128
query_cache_limit =  128 KiB

I suggest using MariaDB / Percona instead of standard MySQL and try again with MYISAM. They improved significantly with Aria ...