Improving Specific MySQL Query to be less CPU/disk intensive

Question

I have a query which is taking a very very long time to execute (like 15 seconds) and this is only on a small test dataset.

I'm looking for help improving this:

describe SELECT * from people where uid in (SELECT uid2 from friends where uid1=PHP_UID_VARIABLE) order by rand() limit 1;
+----+--------------------+---------+------+---------------+------+---------+-------+-------+----------------------------------------------+
| id | select_type        | table   | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |
+----+--------------------+---------+------+---------------+------+---------+-------+-------+----------------------------------------------+
|  1 | PRIMARY            | people  | ALL  | NULL          | NULL | NULL    | NULL  |  6726 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | friends | ref  | uid1,uid2     | uid1 | 8       | const | 15501 | Using where                                  |
+----+--------------------+---------+------+---------------+------+---------+-------+-------+----------------------------------------------+

I know where it's being "Bad" - it's doing both a join and an order by rand() which is never going to be particularly efficient. I'm not sure why it's not using an index on the "people" table - "uid" is the primary key and is indexed.

The purpose of the query should be apparent enough, but for posterity, what I'm doing is selecting 1 random row from the people table where the uid matches the "friends" list in another table.

score 3 · Accepted Answer · answered Feb 04 '11 at 22:04

3

Try this

SELECT p.* 
FROM friends AS f
LEFT JOIN people AS p ON f.uid2 = p.uid
WHERE f.uid1=PHP_UID_VARIABLE
ORDER BY RAND()
LIMIT 1

answered Feb 04 '11 at 22:04

Damp

3,328
20
19

Excellent, that works well. Now to figure out HOW it works better so I write more efficient queries than my original :) – Rafiq Maniar Feb 04 '11 at 22:11
@Rafiq - Don't forget to hit the checkbox under this answer's vote total. This will accept this answer as the one you select as right, and reward the answerer with points. :) – Jared Farrish Feb 04 '11 at 22:13
I've been clicking on it waiting for the StackOverflow 'Accept Timer' to run out :) – Rafiq Maniar Feb 04 '11 at 22:14
Ok, good deal. :) Some of the newer members don't realize they should do that. – Jared Farrish Feb 04 '11 at 22:15

score 0 · Answer 2 · answered Feb 04 '11 at 22:16

I NEVER use IN clauses in production.

EXISTS or NOT EXISTS

Now, some people say it doesn't matter. Some people say it does matter. Experimentation is the true way to determine.

Here is the mysql documentation:

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

My advice is to learn how to ("without thinking about it") write EXISTS and NOT EXISTS clauses, and stick "IN" clauses out to pasture (minus an occasional quick look up of data).

But for production queries, I'd say dump the "IN".

Improving Specific MySQL Query to be less CPU/disk intensive

2 Answers2