CODEWITHSUNDEEP
mysql
Bankette
Bankette

Reputation: 81

MySQL select with where takes a long time

I have a table with about 700.000 rows:

CREATE TABLE IF NOT EXISTS `ext_log_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(8) NOT NULL,
  `logged_at` datetime NOT NULL,
  `object_id` varchar(32) DEFAULT NULL,
  `object_class` varchar(255) NOT NULL,
  `version` int(11) NOT NULL,
  `data` longtext COMMENT '(DC2Type:array)',
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `log_date_lookup_idx` (`logged_at`),
  KEY `log_user_lookup_idx` (`username`),
  KEY `log_class_lookup_idx` (`object_class`),
  KEY `log_version_lookup_idx` (`object_id`,`object_class`,`version`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1219777 ;

I try to run the following query:

SELECT n0_.id AS id0, n0_.action AS action1, n0_.logged_at AS logged_at2, n0_.object_id AS object_id3, n0_.object_class AS object_class4, n0_.version AS version5, n0_.data AS data6, n0_.username AS username7 
FROM ext_log_entries n0_
WHERE n0_.object_id =275634
AND n0_.object_class = 'My\\MyBundle\\Entity\\Field'
AND n0_.version <=1
ORDER BY n0_.version ASC

Here is the MySQL plan:

id             1
select_type    SIMPLE
table          n0_
type           ref
possible_keys  log_class_lookup_idx,log_version_lookup_idx
key            log_class_lookup_idx
key_len        767
ref            const
rows           641159
Extra          Using where; Using filesort

My query need about 37 seconds to be executed for only 1 row in the result... I tried to run the same query by deleting my indexes and it goes a little bit faster : about 31 seconds...

I don't understand why my query is taking so much time and why my indexes don't help the performance? Do you know how I can do to have good performance on this query?

Thanks in advance for your help !

EDIT

Here are the cardinalties of the indexes

log_date_lookup_idx         BTREE  logged_at        1221578 A       
log_user_lookup_idx         BTREE  username         40      A  YES  
log_class_lookup_idx        BTREE  object_class     1010    A       
log_version_lookup_idx      BTREE  object_id        1221578 A  YES  
                                    object_class    1221578 A   
                                    version         1221578 A

Upvotes: 2

Views: 358

Answers (1)

Bankette
Bankette

Reputation: 81

I found a solution, not THE solution, but at least it works for me.

I think it could help anyway all people who are using gedmo loggable and who are lucky (like me) to have objects with only integers IDs.

I changes my column object_id to integer instead of varchar(255). My query now take 0.008 second ! It works for me because i'm sure i'll always have only integers, for people who have varchar, I'm sorry i tried many things but nothing worked....

CREATE TABLE IF NOT EXISTS `ext_log_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(8) NOT NULL,
  `logged_at` datetime NOT NULL,
  `object_id` int(11) DEFAULT NULL,
  `object_class` varchar(255) NOT NULL,
  `version` int(11) NOT NULL,
  `data` longtext COMMENT '(DC2Type:array)',
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `log_date_lookup_idx` (`logged_at`),
  KEY `log_user_lookup_idx` (`username`),
  KEY `log_class_lookup_idx` (`object_class`),
  KEY `log_version_lookup_idx` (`object_id`,`object_class`,`version`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1219777 ;

Upvotes: 2

Related Questions