mysql查询in的优化？

[hehe4569]

mysql> show index from uchome_feed;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| uchome_feed | 0 | PRIMARY | 1 | feedid | A | 23416 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | uid | 1 | uid | A | 1064 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | uid | 2 | dateline | A | 23416 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | dateline | 1 | dateline | A | 23416 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | hot | 1 | hot | A | 38 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | id | 1 | id | A | 1672 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | id | 2 | idtype | A | 2927 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | icon | 1 | icon | A | 101 | NULL | NULL | | BTREE | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)

mysql> desc uchome_feed;
+----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+----------------+
| feedid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| appid | smallint(6) unsigned | NO | | 0 | |
| icon | varchar(30) | NO | MUL | | |
| uid | mediumint(8) unsigned | NO | MUL | 0 | |
| username | varchar(15) | NO | | | |
| dateline | int(10) unsigned | NO | MUL | 0 | |
| friend | tinyint(1) | NO | | 0 | |
| hash_template | varchar(32) | NO | | | |
| hash_data | varchar(32) | NO | | | |
| title_template | mediumtext | NO | | | |
| title_data | mediumtext | NO | | | |
| body_template | mediumtext | NO | | | |
| body_data | mediumtext | NO | | | |
| body_general | mediumtext | NO | | | |
| image_1 | varchar(255) | NO | | | |
| image_1_link | varchar(255) | NO | | | |
| image_2 | varchar(255) | NO | | | |
| image_2_link | varchar(255) | NO | | | |
| image_3 | varchar(255) | NO | | | |
| image_3_link | varchar(255) | NO | | | |
| image_4 | varchar(255) | NO | | | |
| image_4_link | varchar(255) | NO | | | |
| target_ids | mediumtext | NO | | | |
| id | mediumint(8) unsigned | NO | MUL | 0 | |
| idtype | varchar(15) | NO | | | |
| hot | mediumint(8) unsigned | NO | MUL | 0 | |
| keyword | tinyint(4) | NO | | 0 | |
+----------------+-----------------------+------+-----+---------+----------------+
27 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 | 1246 | NULL | NULL | | BTREE | |
| uchome_spacefield | 1 | sex | 1 | sex | A | 6 | NULL | NULL | | BTREE | |
| uchome_spacefield | 1 | birthprovince | 1 | birthprovince | A | 65 | NULL | NULL | | BTREE | |
| uchome_spacefield | 1 | birthcity | 1 | birthcity | A | 415 | NULL | NULL | | BTREE | |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.00 sec)

mysql> desc uchome_spacefield;
+----------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | NO | PRI | 0 | |
| ………………
| friend | mediumtext | NO | | | |
| feedfriend | mediumtext | NO | | | |
| sendmail | mediumtext | NO | | | |
| magicstar | tinyint(1) | NO | | 0 | |
| magicexpire | int(10) unsigned | NO | | 0 | |
| timeoffset | varchar(20) | NO | | | |
| workstatus | set('0','1','2') | NO | | 0 | |
+----------------+-----------------------+------+-----+---------+-------+
35 rows in set (0.00 sec)

mysql>


explain
-> SELECT * FROM uchome_feed
-> WHERE uid IN ('0',146,331,284,145,101,288,321,309,117,52,528,311,77,417,92,33,54,55,44,90,65,484,346,94,322,206,133,6,349,348,160,297,252,277,129,86,143,488,359,365,214,215,370,13,324,98,66,128,151,325,291,287,293,256,87,51,53,113,37,635,483,193,444,407,507,431,383,404,342,344,375,352,8,11,18,19,290,203,216,326,301,228,241,243,278,275,233,3,631,1,190,564,540,89)
-> ORDER BY dateline DESC
-> LIMIT 0,100;
+----+-------------+-------------+-------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | uchome_feed | range | uid | uid | 3 | NULL | 12869 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.01 sec)

其中IN里的是select friend from uchome_spacefield where uid=193;

想问此语句该如何优化？？

[阿_布]

select t1.* from uchome_feed t1,uchome_spacefield t2 where t1.uid=t2.friend and t2.uid=193;

[hehe4569]

追问一下：select * from uchome_feed order by dateline desc limit 0,100;是不是把uchome_feed里的数据都取出来排序后，在分页取100条？？

[ACMAIN_CHM]

引用

追问一下：select * from uchome_feed order by dateline desc limit 0,100;是不是把uchome_feed里的数据都取出来排序后，在分页取100条？？

是

[hehe4569]

2楼，你的语句查出来的记录不全，因为friend类型为text所以只取得第一个好友的动态了