Hi everyone, I have to query 4 tables in order to get the final result. But using left join to query all the tables together is just too slow. If I perform the queries seperately, I will need 200+ SELECT statements inside a foreach loop. Both approaches take about 10 seconds and 5 seconds respectively. Is there anyway to optimize the SQL query? Many thanks.
Show us the query - without knowing what it's like, what indexes you have, whether you've used EXPLAIN on it - there's nothing to go on.
Do they even have to be left joins? Do you know the difference between left and inner?
Thanks Drakla, I've solved the problem by indexing the table. Now only I realize how much indexing can help in speeding up the query. It only takes about 0.22 seconds to perform the query. By the way, is there any downside of indexing other than it takes more spaces?
The only downside is that inserts are slower, so you have to know whether your system is going to be doing mostly inserts, or mostly selects and profile for that. Usually you'll be selecting a whole lot more so indexing is the path to much goodness. E.g. one site I had to clear up had a query that took 33 minutes which ran in under two seconds after indexing.
