CODEWITHSUNDEEP
mysql
M Rusedski
M Rusedski

Reputation: 75

simple mysql query taking 16+ seconds

Simple sql query taking 16 seconds plus. Here is the 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 records. (Originally there were 141 million, I pruned to see if it was the problem)

Query in question is (like)

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 the time part so I removed the time condition and tried following

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

Still the query is taking 16 seconds plus.

Here is my key config params

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

ran out of ideas on this one.

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 heavy disk io. I suspect mysql is retrieving all records to memory.

Upvotes: 4

Views: 339

Answers (3)

Atle
Atle

Reputation: 1877

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'

In this query, you are searching on two columns, time and userid. You do have indexes on both these columns, but MySQL can only use one of them at a time.

MySQL will choose to use either the time-index or the userid index. If it chooses userid, 12738 rows will have to be retrieved, which causes the long query time. MySQL will then search through the retrieved data to filter out the time-column.

The solution is to add a index on both the columns:

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

This way MySQL can search on both userid and time without retrieving data first.

NOTE: It can take a few minutes to create the new index, be patient.

Upvotes: 3

Daniel W.
Daniel W.

Reputation: 32260

But the query only with userid was still taking almost same amount of time.

You can't do much more. What are your hardware specs? My configs are average and higher 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 to use MariaDB / Percona instead of standard MySQL and try again with MYISAM. They improved very much with Aria...

Upvotes: 0

Rizwan Sultan
Rizwan Sultan

Reputation: 187

Try to make indexing in time and userid.you can easily built index using phpmyadmin or sqlyog.

Upvotes: 0

Related Questions