which one run faster in mysql: a query with multiple joins or that query with using nested select?
+5
A:
it's impossible to answer in this case. you should analyze each of them with EXPLAIN
zerkms
2010-04-12 03:45:04
here is the query:(it returns 60,000 records)SELECT @row := @row + 1 AS rowno,t2.no,t3.fname, t3.lame,t2.syear, t2.fcode ,t2.flcode,t2.pid FROM t2 JOIN t3 ON t3.pid=t2.pid JOIN t4 USING(stid) JOIN t5 ON t4.ecode=t5.ecode WHERE t5.cat='1' ORDER BY t3.lname,t3.lnameall tables have indexes.i haven't tested nested select yet.
hd
2010-04-12 04:37:03
Please post a new question... I imagine it's taking a while doing a filesort of 60k records, it will depend on the row size and many other factors, please post a new question with the query, schema of all tables and the explain plan.
MarkR
2010-04-13 22:35:57
+1
A:
Given the correct Indexes on the tables, I would go with saying JOINS would be more performant, but it is always best practice to test the various queries for performance.
astander
2010-04-12 03:46:11
A:
In the majority of cases, JOINS are more efficient. This assumes you're joining on columns which are indexed. HOwever, the above answers are right. You need to evaluate your particular query with EXPLAIN to see.
pocketfullofcheese
2010-04-12 03:56:55