当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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 | |

+----+-------------+-------+-------+---------------+-------------+---------+-----------+------+--------------------------+

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: