mysql query LIKE '%%' sql 效能调优记录(二)

对LIKE '%%'这种与不加like的过滤导致sql性能差别很大

 

mysql> select  com_id ,company.name,count(*) as albums_count from company_albums ,company
    -> where company_albums.com_id=company.id  AND company_albums.name LIKE '%%'
    -> group by company.name
    -> order by albums_count desc limit 31;

 

+--------+-----------------------+--------------+
| com_id | name                  | albums_count |
+--------+-----------------------+--------------+
------

31 rows in set (4.41 sec)

mysql> explain select  com_id ,company.name,count(*) as albums_count from company_albums ,company
    -> where company_albums.com_id=company.id  AND company_albums.name LIKE '%%'
    -> group by company.name
    -> order by albums_count desc limit 31;
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+----------------------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                                | rows  | Extra                                        |
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | company_albums | ALL    | com_idx       | NULL    | NULL    | NULL                               | 72441 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | company        | eq_ref | PRIMARY       | PRIMARY | 4       | test_01.company_albums.com_id |     1 |                                              |
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

 

mysql> select  com_id ,company.name,count(*) as albums_count from company_albums ,company where company_albums.com_id=company.id  group by company.name order by albums_count desc limit 31;
+--------+-----------------------+--------------+
| com_id | name                  | albums_count |
+--------+-----------------------+--------------+
------

31 rows in set (0.08 sec)