How to optimize the MySQL query for large tables

advertisements

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());

Thanks


#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);