I have performance problem with my large table. I have to make index and maybe change query to get best performance.

Is best way to make two column index (DeviceID,GPStimestamp)?

Table create:

CREATE TABLE `DEVICES_GPS` (
`Counter` int(10) unsigned NOT NULL auto_increment,
`GPStimestamp` int(10) unsigned NOT NULL,
`DeviceID` bigint(15) unsigned NOT NULL,
`Lat` decimal(9,4) NOT NULL default '0.0000',
`Lon` decimal(9,4) NOT NULL default '0.0000',
`Speed` decimal(5,2) unsigned NOT NULL default '0.00',
`Alt` int(6) unsigned NOT NULL default '0',
`Streetname` varchar(40) NOT NULL default '-',
`Streetnumber` int(5) unsigned NOT NULL default '0',
`Zip` varchar(10) NOT NULL default '-',
`City` varchar(30) NOT NULL default '-',
`Country` varchar(30) NOT NULL default '-',
`Resolved` tinyint(1) unsigned NOT NULL default '0',
`Alarm` tinyint(1) unsigned NOT NULL default '0',
`ServerTimestamp` int(10) unsigned NOT NULL default '0',
`gps_srv_timestamp` int(10) unsigned NOT NULL default '0',
`Course` decimal(5,2) unsigned NOT NULL default '0.00',
`LastIO` char(4) NOT NULL default '0x04',
`Mileage` decimal(10,3) unsigned NOT NULL default '0.000',
PRIMARY KEY (`Counter`),
KEY `FK_dgps_deviceid` (`DeviceID`),
CONSTRAINT `FK_dgps_deviceid` FOREIGN KEY (`DeviceID`) REFERENCES `DEVICES` (`DeviceID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10472322 DEFAULT CHARSET=utf8

Here is my typical query on that table

EXPLAIN SELECT Counter, GPStimestamp, LastIO FROM DEVICES_GPS WHERE DeviceID=5631010063 AND GPStimestamp>1327675993 AND GPStimestamp<1327740517 ORDER By GPStimestamp;
+----+-------------+-------------+------+-------------------------------+------------------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------------+------------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | DEVICES_GPS | ref | FK_dgps_deviceid | FK_dgps_deviceid | 8 | const | 148784 | Using where; |
+----+-------------+-------------+------+-------------------------------+------------------+---------+-------+--------+-----------------------------+

What is best index on that table and do I have to change my query?

THANKS!

Before looking whether you need more indexes or not you need to look at your data. For example, in the query above 148784 rows were examined. How many rows were actually returned? If the number of rows returned was about the same number then no additional indexes are required. If the number of rows returned was substantially lower than the number examined then a different index may be necessary.

There are several choices for indexes here:

1. An index on deviceid - this is already there but you need to determine whether this is good enough
2. An index on GPStimestamp but this too may return too many rows for the same timestamp or range of timestamps
3. An index on both (deviceid and GPStimestamp) which narrows down the range

Keep in mind that the larger the indexes the more you will be penalized in terms of storage capacity and also in terms of time on INSERT, UPDATE and DELETE statements against the table.

Here is query reply:

mysql> SELECT Counter, GPStimestamp, LastIO FROM DEVICES_GPS WHERE DeviceID=5631010063 AND GPStimestamp>1327675993 AND GPStimestamp<1327740517 ORDER By GPStimestamp;
Empty set (29 min 34.48 sec)

This take very LONG!! 29 minute!

Is best make number 3? "3. An index on both (deviceid and GPStimestamp) which narrows down the range"

Thanks.