mysql - sql问题请教

Question

select a.,b from count a, user b where a.aid = b.aid order by b.count desc limit 10

在count字段中排名前10的字段，找出aid相同的另外一个表的所有字段。
这句能实现，但效率太低，几万记录查询都几秒了。有简单的办法吗

Answer 1

如果你是是用Mysql，我觉得可以参考下这里的内容
https://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

To increase ORDER BY speed, check whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies:

Increase the sort_buffer_size variable value.

Increase the read_rnd_buffer_size variable value.

Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them. For example, CHAR(16) is better than CHAR(200) if values never exceed 16 characters.

Change the tmpdir system variable to point to a dedicated file system with large amounts of free space. The variable value can list several paths that are used in round-robin fashion; you can use this feature to spread the load across several directories. Paths should be separated by colon characters (“:”) on Unix and semicolon characters (“;”) on Windows, NetWare, and OS/2. The paths should name directories in file systems located on different physical disks, not different partitions on the same disk.

除此之外，也可以试试复合索引，具体可以看看这里
http://www.cnblogs.com/anywei/archive/2011/12/12/mysql.html