0

I have a table for Items and then a table for Parameters where each Item can have multiple Parameters. Each Parameter object has a foreign key back to its Item. I also have a Parameter Type table that stores the possible types of Parameters.

Here is the table info for my Parameter table:

CREATE TABLE 'parameter' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'type_id' int(11) NOT NULL,
  'value' longtext NOT NULL,
  'item_id' int(11) NOT NULL,
  PRIMARY KEY ('id'),
  KEY 'parameter_403d8ff3' ('type_id'),
  KEY 'parameter_0a47aae8' ('item_id'),
  CONSTRAINT 'item_id_refs_id_eeb3b028' FOREIGN KEY ('item_id') REFERENCES 'tracker_item' ('id'),
  CONSTRAINT 'type_id_refs_id_53b2638a' FOREIGN KEY ('type_id') REFERENCES 'parametertype' ('id'),
) ENGINE=InnoDB AUTO_INCREMENT=31445609 DEFAULT CHARSET=utf8;

And here is my Parameter Type table:

CREATE TABLE 'parametertype' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'name' varchar(60) NOT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'name' ('name'),
) ENGINE=InnoDB AUTO_INCREMENT=309 DEFAULT CHARSET=utf8;

I have two Items, IDs 115711 and 116792, for which I want to do this query:

SELECT p.`id`,
       p.`type_id`,
       p.`value`,
       p.`item_id`,
       p.`upload_id`,
       p.`creator_id`,
       p.`date_recorded`,
       p.`date_last_modified`,
       p.`date_added`
FROM parameter p
INNER JOIN parametertype pt ON p.`type_id` = pt.`id`
WHERE p.`item_id` = <ITEM_ID> 
  AND pt.`name` LIKE 'some_name' 
ORDER BY p.`date_recorded` DESC

When I do this for Item 116792, it takes 5.7ms. When I do it for Item 115711, it takes 9 SECONDS. The EXPLAIN of each query is the same except for the "ref" column -- for the fast query, it shows const in that column for the parameter table entry, where as for the slow query, it shows parametertype.id. Also, in the key column. The slow query shows data_parameter_403d8ff3 there (the key for the Parameter Type table) whereas the fast query shows data_parameter_0a47aae8 there (the key for the Item table).

What does this mean and why is one so slow? Is there anything I can do to make it as fast as the other query?

For context, Item 116792 has 560 Parameter entries and Item 115711 has 823 Parameter entries. The Parameter Type table has 237 entries.

The full EXPLAIN query for Item 116792:

    +----+-------------+--------------------+-------+-------------------------------------------------+-------------------------+---------+-------+------+-----------------------------------------------------------+
    | id | select_type | table              | type  | possible_keys                                   | key                     | key_len | ref   | rows | Extra                                                     |
    +----+-------------+--------------------+-------+-------------------------------------------------+-------------------------+---------+-------+------+-----------------------------------------------------------+
    |  1 | SIMPLE      | parametertype      | range | PRIMARY,name                                    | name                    | 182     | NULL  |    1 | Using where; Using index; Using temporary; Using filesort |
    |  1 | SIMPLE      | parameter          | ref   | parameter_403d8ff3,parameter_0a47aae8           | parameter_0a47aae8      | 4       | const |  559 | Using where                                               |
    +----+-------------+--------------------+-------+-------------------------------------------------+-------------------------+---------+-------+------+-----------------------------------------------------------+

The full EXPLAIN query for Item 115711:

    +----+-------------+--------------------+-------+-------------------------------------------------+-------------------------+---------+-------------------------------+------+-----------------------------------------------------------+
    | id | select_type | table              | type  | possible_keys                                   | key                     | key_len | ref                           | rows | Extra                                                     |
    +----+-------------+--------------------+-------+-------------------------------------------------+-------------------------+---------+-------------------------------+------+-----------------------------------------------------------+
    |  1 | SIMPLE      | parametertype      | range | PRIMARY,name                                    | name                    | 182     | NULL                          |    1 | Using where; Using index; Using temporary; Using filesort |
    |  1 | SIMPLE      | parameter          | ref   | parameter_403d8ff3,parameter_0a47aae8           | parameter_403d8ff3      | 4       | parametertype.id              |  676 | Using where                                               |
    +----+-------------+--------------------+-------+-------------------------------------------------+-------------------------+---------+-------------------------------+------+-----------------------------------------------------------+
10
  • Did you try running the query with and without cache? dev.mysql.com/doc/refman/5.0/en/query-cache-in-select.html Commented Aug 6, 2015 at 8:25
  • Thanks for the suggestion, but that didn't seem to affect it
    – samskeller
    Commented Aug 6, 2015 at 8:32
  • It was just to get a better idea of the true time each query takes Commented Aug 6, 2015 at 8:37
  • You might also want to check your innodb configuration, if you haven't done so already. Doesn't help with your query, but might speed things up globally. dba.stackexchange.com/questions/27328/… Commented Aug 6, 2015 at 8:40
  • Are you really sure pan from EXPLAIN is the same? Aren't the rows swapped or something? For the optimizer to use parametertype.id it has to know it, so read pt first, the other plan seems to use the const from the query itself. You can try EXPLAIN EXTENDED <query>; SHOW WARNINGS; too to see a bit more.
    – jkavalik
    Commented Aug 6, 2015 at 11:32

1 Answer 1

0
ALTER TABLE parameter ADD INDEX(item_id, date_recorded)

Please provide EXPLAIN SELECT ... for both the slow and fast queries.

4
  • Thanks, I've updated the description to include the full output of the EXPLAIN queries
    – samskeller
    Commented Aug 6, 2015 at 20:23
  • Added the index and used "USE INDEX" and that appears to have worked! In fact, it appears that even if I just use the existing Item Index that it goes as fast as it should. Thanks!
    – samskeller
    Commented Aug 7, 2015 at 8:37
  • @samskeller probably your query is selecting only a few rows so filesort is not slow - but this index is better and should work without the USE INDEX
    – jkavalik
    Commented Aug 7, 2015 at 10:13
  • 1
    Even faster might be INDEX(item_id, type_id, date_recorded) or INDEX(type_id, item_id, date_recorded)
    – Rick James
    Commented Aug 7, 2015 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.