CODEWITHSUNDEEP
sqlmysqljoinsubquerymyisam
freddiefujiwara
freddiefujiwara

Reputation: 59069

MySQL EXPLAIN type:ALL problem

I have the following tables:

CREATE TABLE `categories` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `path` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '/',
 PRIMARY KEY (`id`),
 UNIQUE KEY `path` (`path`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `category_id` int(11) NOT NULL DEFAULT '1',
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no name',
 PRIMARY KEY (`id`),
 KEY `category_id` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And the query is matter:

SELECT c.path, i.name
FROM categories c
JOIN items i ON i.category_id = c.id
WHERE c.path LIKE CONCAT( (
 SELECT path
 FROM categories ci
 WHERE ci.id =2
),  '/%' ) 
OR c.id =2
mysql> EXPLAIN SELECT c.path, i.name FROM categories c JOIN items i ON i.category_id = c.id WHERE c.path LIKE CONCAT( (  SELECT path FROM categories ci WHERE ci.id =2 ),  '/%' )  OR c.id =2
   -> ;
        +----+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------+
        | id | select_type | table | type   | possible_keys | key     | key_len | ref                | rows | Extra       |
        +----+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------+
        |  1 | PRIMARY     | i     | ALL    | category_id   | NULL    | NULL    | NULL               |    8 |             |
        |  1 | PRIMARY     | c     | eq_ref | PRIMARY,path  | PRIMARY | 4       | tree.i.category_id |    1 | Using where |
        |  2 | SUBQUERY    | ci    | const  | PRIMARY       | PRIMARY | 4       |                    |    1 |             |
        +----+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------+

The 'category_id''s type is ALL; How to not use type ALL?

Do you have best solutions?

Addition: I tried on InnoDB

mysql> EXPLAIN SELECT c.path, i.name FROM categories c JOIN items i ON i.category_id = c.id WHERE c.path LIKE CONCAT( (  SELECT path FROM categories ci WHERE ci.id =2 ),  '/%' )  OR c.id =2;
+----+-------------+-------+-------+---------------+-------------+---------+-----------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref       | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------+---------+-----------+------+--------------------------+
|  1 | PRIMARY     | c     | index | PRIMARY,path  | path        | 767     | NULL      |    7 | Using where; Using index |
|  1 | PRIMARY     | i     | ref   | category_id   | category_id | 4       | tree.c.id |    1 |                          |
|  2 | SUBQUERY    | ci    | const | PRIMARY       | PRIMARY     | 4       |           |    1 |                          |
+----+-------------+-------+-------+---------------+-------------+---------+-----------+------+--------------------------+

Upvotes: 2

Views: 4083

Answers (1)

goat
goat

Reputation: 31834

try 

 create index idx_id_path on categories(id, path)

(path, id) may or may not work better

often, mysql only uses one index per table, so having additional columns in the index, may allow it to be used better if the index has the right columns, in the right order, for that specific query.

Upvotes: 2

Related Questions