I am trying to optimize the following query as it is taking an extremely long time to execute. Can anyone provide any advice on how to optimize this and can they recommend any indexing that would speed it up. As a note the edata table contains around 1 million rows and the ddata table has around 15 million rows. There are around 5,000 items selected from ddata if you run the query
SELECT * FROM ddata WHERE DATE(startDate) = DATE(NOW());
The query that I am trying to optimize is:
SELECT e.ID,e.uID,e.sID FROM edata e LEFT JOIN ddata d ON e.sID=d.sID WHERE DATE(d.startDate)=DATE(NOW());
#1: You probably don't want an Outer Join, so replace it with an Inner Join (MySQL's optimizer is known to be weak determining if an Outer Join can be rewritten as an Inner Join).
#2: Remove the function on d.startDate.
SELECT e.ID,e.uID,e.sID FROM edata e JOIN ddata d ON e.sID=d.sID WHERE d.startDate >= DATE(NOW()) AND d.StartDate < date_add(DATE(NOW(), interval 1 days);