Get help on this challenge

Debugging17


Please read this explanation before attempting debugging challenges.


I noticed my query is a little slow, so I checked which indices my MySQL database was using and for found that for some reason it's not using any index for one of the joins (that '300' should say '1'). But it should use the index. What's wrong here?

--Challenge by ynori7
mysql> explain SELECT *  FROM Thing t, Category c, Thingy_Category_Mapping tcm WHERE t.content_id = tcm.thing_id AND t.language = tcm.thing_language AND tcm.category_id=c.id AND  t.content_id='1' AND t.language='en';
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
|  1 | SIMPLE      | t     | const  | PRIMARY       | PRIMARY | 153     | const,const          |    1 |                          |
|  1 | SIMPLE      | tcm   | index  | NULL          | PRIMARY | 57      | NULL                 |  300 | Using where; Using index |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | test.tcm.category_id |    1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+


mysql> show create table Category;
+----------+------------------------------------------------------------------------+
| Table    | Create Table                                                           |
+----------+------------------------------------------------------------------------+
| Category | CREATE TABLE `Category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=utf8                             |
+----------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table Thingy_Category_Mapping;
+-------------------------+---------------------------------------------------------+
| Table                   | Create Table                                            |
+-------------------------+---------------------------------------------------------+
| Thingy_Category_Mapping | CREATE TABLE `Thingy_Category_Mapping` (
  `thing_id` varchar(50) NOT NULL,
  `thing_language` enum('en','de','es') NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`thing_id`,`thing_language`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1                                              |
+-------------------------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table Thing;
+-------------------------+---------------------------------------------------------+
| Table                   | Create Table                                            |
+-------------------------+---------------------------------------------------------+
| Thing | CREATE TABLE `Thing` (
  `content_id` varchar(50) NOT NULL,
  `language` enum('en','de','es') NOT NULL,
  `data` varchar(200) NOT NULL,
  PRIMARY KEY (`content_id`,`language`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8                                                |
+-------------------------+---------------------------------------------------------+
1 row in set (0.00 sec)

You must be logged in to play.



Challenge by ynori7.