mysql 语句，球分析

This topic created in 3595 days ago, the information mentioned may be changed or developed.

explain select `id` from `ao_articles` where (select count(*) from `ao_articles_tags` inner join `ao_articles_relationships` on `ao_articles_tags`.`id` = `ao_articles_relationships`.`tag_id` where `ao_articles_relationships`.`cid` = `ao_articles`.`id` and `id` in (1,2,3,4,5)) >= 1 limit 10000

应该已经添加索引，为什么还会扫描全表？

Supplement 1 · Aug 2, 2016

上面是 Eloquent 自动生成的 join ，使用构造器重写效率提高

```mysql
DB::table('articles')
->join('articles_relationships', 'articles_relationships.cid', '=', 'articles.id')
->join('articles_tags', 'articles_relationships.tag_id', '=', 'articles_tags.id')
->select('articles.id')
->whereIn('articles_tags.id', [1,2,3])
->lists('id');
```

2 replies • 2016-08-02 16:49:06 +08:00

naver1

Aug 2, 2016

我一直没搞明白索引。。。帮不了忙~

huixia0010

Aug 2, 2016

count(*) 是全表扫描,自己百度下一目了然~