联表查询求救

Question

1 like的全表扫描没办法避免 必然快不了
2 贴下两个sql的explain  和表结构

Answer 1

down+----+-------------+-------+------+----------------+----------------+---------+------------+-------+-------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref        | rows  | Extra       |
+----+-------------+-------+------+----------------+----------------+---------+------------+-------+-------------+
|  1 | SIMPLE      | a     | ALL  | PRIMARY        | NULL           | NULL    | NULL       | 55777 | Using where |
|  1 | SIMPLE      | b     | ref  | downsmid_index | downsmid_index | 4       | films.a.id |     1 | Using index |
+----+-------------+-------+------+----------------+----------------+---------+------------+-------+-------------+more
+----+-------------+-------+--------+---------------+--------------+---------+-------------+-------+-------------+
| id | select_type | table | type   | possible_keys | key          | key_len | ref         | rows  | Extra       |
+----+-------------+-------+--------+---------------+--------------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | b     | index  | moremidindex  | moremidindex | 4       | NULL        | 63305 | Using index |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY      | 4       | films.b.mid |     1 | Using where |
+----+-------------+-------+--------+---------------+--------------+---------+-------------+-------+-------------+down表
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       | NO   | PRI | NULL    | auto_increment |
| mid      | int(11)       | NO   | MUL | NULL    |                |
| down1    | varchar(3000) | YES  |     |         |                |
| down2    | varchar(3000) | YES  |     |         |                |
| down3    | varchar(2000) | YES  |     |         |                |
| down4    | text          | YES  |     |         |                |more
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| mid           | int(11)      | NO   | MUL | NULL    |                |
| tags          | varchar(100) | YES  |     |         |                |
| hits          | int(11)      | YES  |     | 0       |                |
| hot           | tinyint(4)   | YES  |     | 0       |                |
| push          | tinyint(4)   | YES  |     | 0       |                |
| cinema        | tinyint(4)   | YES  |     | 0       |                |
| views         | int(11)      | YES  |     | 0       |                |
下面还有几个int字段和varchar一个字段

Answer 2

sql语句分别是
explain select count(1) from libs a,more b where a.id=b.mid and title='eq';
explain select count(1) from libs a,down b where a.id=b.mid and title='eq';