Data_length + index_length are greater than innodb_buffer_size, and I have a huge table where the select took longer than 5 minutes. Should I resolve this issue by adding another index on a column, or it will just getting worse?

Here is the table schema:

| record | CREATE TABLE `record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `altitude` smallint(6) DEFAULT NULL,
  `counter` double DEFAULT NULL,
  `datetime` datetime NOT NULL,
  `details` longtext,
  `deviation` smallint(6) DEFAULT NULL,
  `engine` smallint(6) DEFAULT NULL,
  `event` smallint(6) DEFAULT NULL,
  `fuel_level` double DEFAULT NULL,
  `gsm_level` int(11) DEFAULT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `satellite` smallint(6) DEFAULT NULL,
  `speed` smallint(6) DEFAULT NULL,
  `time` time NOT NULL,
  `treated` tinyint(1) NOT NULL DEFAULT '0',
  `id_driver` bigint(20) DEFAULT NULL,
  `id_vehicle_device` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_ai9y9d52lx1xvtaiv3w0x60a5` (`id_driver`),
  KEY `FK_ilwlg0mt87bxyemx4tov1elni` (`id_vehicle_device`),
  KEY `treated_record` (`treated`),
  CONSTRAINT `FK_ai9y9d52lx1xvtaiv3w0x60a5` FOREIGN KEY (`id_driver`) REFERENCES `driver` (`id`),
  CONSTRAINT `FK_ilwlg0mt87bxyemx4tov1elni` FOREIGN KEY (`id_vehicle_device`) REFERENCES `vehicle_device` (`id`)

here is the select :

select count(*) from record where datetime between '2017-07-01' and '2017-07-30' and id_vehicle_device = 2;

and here is the explain select :

| id | select_type | table  | partitions | type | possible_keys                | key                          | key_len | ref   | rows   | filtered | Extra       

|  1 | SIMPLE      | record | NULL       | ref  | FK_ilwlg0mt87bxyemx4tov1elni | FK_ilwlg0mt87bxyemx4tov1elni | 8       | const | 264902 |    11.11 | Using where 

This query SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB FROM information_schema.tables WHERE engine='InnoDB'; give me :

| InnoDBSizeMB |
|       375462 |

while innodb_log_file_size = 32G.

  • 2
    In order to say we'd need more information. What does your table structure look like? What is the select statement that is taking a long time? What indexes do you already have? – indiri Aug 24 '17 at 16:01
  • 1
    Please provide the SELECT, EXPLAIN SELECT ..., and SHOW CREATE TABLE. Performance improvement may be as simple as adding a composite index. – Rick James Aug 25 '17 at 5:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.