How can i improve mysql join query speed?
I have two tables lets say employees and order, both table have millions of records.
Select orders.*
from orders
INNER JOIN employees
on Employees.id = orders.employeeid
WHERE orders.type='daily'
and orders.date > Employees.registerDate
and orders.date < Employees.regiserDate + Interval 60 Days
The above query is rough query, there may be syntax error, but is just considerations.
开发者_高级运维The query consuming almost 60 seconds to load, any body know how can i optimize this query
The indexing is one of the best options mentioned, you could also use stored procedure to optimize its performance on retrieving data.
you may would also like to use the ANALYZE statement to optimize the retrieval of data from those tables you may read more about this statement from this site:
http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
Set your indexes right (which you probably did?) or create views. You could also consider a temp table, but most of the time the sync makes it not worth it.
精彩评论