MySQL query performance between two options

Question

I have written two queries which return the same result, but I want use which is performance wise better

SELECT ID, INTERESTS 
FROM users 
WHERE CITY = (SELECT CITY FROM users WHERE ID = 124)

SELECT b.ID, b.INTERESTS 
FROM users a, users b 
WHERE a.CITY = b.CITY AND a.ID = 124

Test it on production size data. If you can't tell, it doesn't matter. Also, you should learn to use ANSI joins - they've been in the SQL standard since 1992. — Blorgbeard, Mar 17 '16 at 19:32

score 0 · Answer 1 · answered Mar 17 '16 at 19:52

First, you should stop using archaic syntax with commas in the FROM clause. Simple rule: Never use commas in the FROM clause; always use explicit JOIN syntax.

Second, a performance question is best handled by checking performance on your data on your system.

Your second query is properly written as:

SELECT b.ID, b.INTERESTS
FROM users a JOIN
     users b 
     ON a.CITY = b.CITY AND a.ID = 124 ;

This should have fine performance. Sometimes EXISTS can have slightly better performance:

select u2.*
from users u
where exists (select 1 from users u where u.city = u2.city and and u.id = 124);

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

A couple of thoughts

For your two queries, look at the EXPLAIN plan to see how mysql is limiting the data

You can do this by running

EXPLAIN SELECT ID,INTERESTS FROM users WHERE CITY = (SELECT CITY FROM users WHERE ID=124)
EXPLAIN SELECT b.ID,b.INTERESTS FROM users a,users b WHERE a.CITY = b.CITY AND a.ID=124

I've found this to be a hepful read when it comes to optimizing queries based on their explain plan.

For good performance, since you are basically joining on the CITY column, consider creating an index on the CITY column in the users table.

Indexes are a trade-off between space and performance.

You can create one by running

CREATE INDEX idx_city ON users(CITY);

Doing this should make both queries perform well.

MySQL query performance between two options

2 Answers2

A couple of thoughts