Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: InnoDB index and query problem (large table)

    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!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    Quote Originally Posted by it-iss.com View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •