CODEWITHSUNDEEP
mysqlquery-optimization
MMMMS
MMMMS

Reputation: 2201

Mysql - speed up select query from 2 million rows

I have a table like below,

Field    Type      Null Key Default               Extra

id       bigint(11) NO  PRI NULL                  auto_increment
deviceId bigint(11) NO  MUL NULL        
value    double     NO      NULL        
time    timestamp   YES MUL 0000-00-00 00:00:00

It has more than 2 million rows. When I run select * from tableName; It takes more than 15 mins.

When I run select value,time from sensor_value where time > '2017-05-21 04:47:48' and deviceId>=812; It takes more than 45 sec to load.

Note : 512 has more than 92514 rows. Even I have added index for column like below,

ALTER TABLE `sensor_value` 
 ADD INDEX `IDX_FIELDS1_2` (`time`, `deviceId`) ;

How do I make select query fast?(load in 1sec) Am I doing indexing wrong?

Upvotes: 2

Views: 6829

Answers (2)

Rick James
Rick James

Reputation: 142218

Only 4 columns? Sounds like you have very little RAM, or innodb_buffer_pool_size is set too low. Hence, you were seriously I/O-bound and/or swapping.

WHERE time > '2017-05-21 04:47:48'
  AND deviceId >= 812

is two ranges. There is no thorough way to optimize that. Either of these would help. If you have both, the Optimizer might pick the better one:

INDEX(time)
INDEX(deviceId)

When using a 'secondary' index in InnoDB, the query first looks in the index BTree; when there is a match there, it has to look up in the 'data' BTree (using the PRIMARY KEY for lookup).

Some of the anomalous times you saw when trying INDEX(time, deviceId) were because the filtering kept from having to reach over into the data as often.

Do you use id for anything other than uniqueness? Is the pair deviceId & time unique? If the answers are 'no' and 'yes', then get rid of id and change to PRIMARY KEY(deviceId, time). Or you could swap those two columns. What other queries do you have?

Getting rid of id shrinks the table some, thereby cutting down on I/O.

Upvotes: 2

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

When using combined index usually you must use equality operator on first column and then you can use range criteria on second column. So I recommend you change the order of columns in your index like this:

ALTER TABLE `sensor_value` 
 ADD INDEX `IDX_FIELDS1_2` (`deviceId`, `time`) ;

then change to use equal sign for deviceId(use deviceId=812 not deviceId>=812):

select value,time from sensor_value where time > '2017-05-21 04:47:48' and deviceId=812;

I hope it could help.

2 million records is not much for Mysql and it is normal to get result in less than 1 sec for 1 billion records if you do the right things.

Upvotes: 1

Related Questions