SQL慢查询优化

Question

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> 应该如何优化？？

Answer 1

创建 field.sex 索引？？那可以避免排序么？就是viewnum的排序操作？

Answer 2

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

Answer 3

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

Answer 4

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