我有以下MySql查询
SELECT DISTINCT cats.name AS cat_name, cats.alias AS cat_alias, content.catid AS cat_id, content.title AS title, content.introtext AS text, content.created AS date, content.publish_up AS date_publish, content.id AS ID, content.alias AS alias, content.hits
AS hits, content.plugins AS plugins
FROM t0nbl_k2_items AS content LEFT JOIN t0nbl_k2_categories AS cats ON cats.id = content.catid
WHERE content.trash = 0
AND cats.access <=3
AND content.access <=3
AND content.published=1
AND cats.published=1
AND ( content.publish_up='0000-00-00 00:00:00' OR content.publish_up <='2014-10-31 00:00:00' )
AND ( content.publish_down='0000-00-00 00:00:00' OR content.publish_down>= '2014-10-31 00:00:00' )
ORDER BY content.created ASC
LIMIT 0,12;
花了4秒钟才得出结果。
解释说明显示以下
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE content range item,catid,item_categ,latest item 10 NULL 11858 Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE cats eq_ref PRIMARY,category,published,access,item_categ PRIMARY 4 site2.content.catid 1 Using where
下面是索引。
INDEXNAME FIELDNAME
PRIMARY id
item published
publish_up
publish_down
trash
access
catid catid
created_by created_by
ordering ordering
featured featured
created created
language language
item_categ access
published
publish_up
publish_down
catid
created
latest publish_up
publish_down
created
我不知道如何优化这个查询来加快它的速度。#_k2_items有30000个项目。正如你所看到的,它并不是那么大的数据。那么,我应该定义哪些索引来让Mysql使用它们呢?
很抱歉我的英语不好,代码格式也不好。我是新来使用这个编辑器的。
这是查询:
SELECT DISTINCT cats.name AS cat_name, cats.alias AS cat_alias, content.catid AS cat_id,
content.title, content.introtext AS text, content.created AS date,
content.publish_up AS date_publish, content.id, content.alias, content.hits, content.plugins
FROM t0nbl_k2_items content JOIN
t0nbl_k2_categories cats
ON cats.id = content.catid
WHERE content.trash = 0 AND cats.access <=3 AND content.access <=3 AND content.published=1 AND
cats.published=1 AND
( content.publish_up='0000-00-00 00:00:00' OR content.publish_up <='2014-10-31 00:00:00' ) AND
( content.publish_down='0000-00-00 00:00:00' OR content.publish_down>= '2014-10-31 00:00:00' )
ORDER BY content.created ASC
LIMIT 0,12;
distinct
是性能上的一大障碍。如果可以删除它,就删除它。接下来,left join
将被where
子句撤消。因此,您不妨将其表示为inner join
。
我建议对t0nbl_k2_items(trash, published, access, publish_up, publish_down, catid)
和t0nbl_k2_categories(catid, access)
进行索引。有了distinct
,就没有办法消除最终的order by
。即使没有distinct
,我也不确定是否有办法。