SQL慢查询优化

[hehe4569]

SELECT main.*, field.* FROM uchome_space main, uchome_spacefield field
WHERE field.sex='1' AND field.uid=main.uid
ORDER BY main.viewnum DESC LIMIT 0,100;

此语句查询时很慢，主要在排序时慢

mysql> explain SELECT main.*, field.* FROM uchome_space main, uchome_spacefield field
-> WHERE field.sex='1' AND field.uid=main.uid
-> ORDER BY main.viewnum DESC LIMIT 0,100;
+----+-------------+-------+--------+---------------+---------+---------+------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------------+--------+----------------+
| 1 | SIMPLE | main | ALL | PRIMARY | NULL | NULL | NULL | 361996 | Using filesort |
| 1 | SIMPLE | field | eq_ref | PRIMARY | PRIMARY | 3 | gy_home.main.uid | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------------------+--------+----------------+
2 rows in set (0.01 sec)

mysql> show index from uchome_space;
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| uchome_space | 0 | PRIMARY | 1 | uid | A | 362055 | NULL | NULL | | BTREE | |
| uchome_space | 1 | username | 1 | username | A | 362055 | NULL | NULL | | BTREE | |
| uchome_space | 1 | domain | 1 | domain | A | 1 | NULL | NULL | | BTREE | |
| uchome_space | 1 | ip | 1 | ip | A | 6465 | NULL | NULL | | BTREE | |
| uchome_space | 1 | updatetime | 1 | updatetime | A | 815 | NULL | NULL | | BTREE | |
| uchome_space | 1 | mood | 1 | mood | A | 53 | NULL | NULL | | BTREE | |
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)

mysql> show index from uchome_spacefield;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| uchome_spacefield | 0 | PRIMARY | 1 | uid | A | 362066 | NULL | NULL | | BTREE | |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql>

应该如何优化？？

[ACMAIN_CHM]

创建 field.sex 索引

[hehe4569]

创建 field.sex 索引？？

那可以避免排序么？就是viewnum的排序操作？

[ACMAIN_CHM]

没有办法避免排序。

[hehe4569]

我在viewnum上创建索引 效果会好些么？

[ACMAIN_CHM]

不会。

[gpogpo]

数据量非常大的情况下最好不要用SELECT ×来检索
把字段名称都写出来会好些

[liang8305]

mark一下；
改天来看看

[liqiang8]

AND field.uid=main.uid 这里最好改成inner join