Query takes too long to fetch data

Question

I am aware of the slow query log but isnt this just for queries that exceed the maximum execution time? My question is slightly different (i think).

I am running the below query and to me it doesnt seem like it should present a problem. But when i run it, the "Running" time is over 20 seconds and then it sits there "Fetching" for ages! To the point where i have to stop it due to DB performance issues.

Can anyone explain where i am going wrong here. Coding newbie so please be gentle.

SELECT 
t1.oid,
(SELECT 
        DATE_FORMAT(t2.date,
                    '%d/%m/%y') AS 'AVF Date'
    FROM
        t2
    WHERE
        t1.oid = t2.fk_oid
            AND t2.type = '-25'
            AND YEAR(t2.date) BETWEEN 2005 AND 2014
    ORDER BY t2.date ASC
    LIMIT 1) AS 'AVF Date',
t2.site 
FROM
t1
left join t2 on t1.oid=t2.fk_oid

UPDATE - Ok so what i need is as follows. We have a patient database and these patients have procedures recorded on this database. I am trying to return the patient oid (t1.oid) along with the FIRST procedure that they had (if it was between 2005 and 2014) and where they had it (t2.date and t2.site respectively), but only if the procedure is of a particular type (t2.type = '-25')

So basically. All the patients who had an AVF between 2005 and 2014 along with the "site" of the AVF (which in this case is left arm/right arm etc.)

I hope this makes it a bit clearer.

Thanks in advance.

Mikey

Answer 1

I think you can use only the join without the subquery in the select, have a try:

SELECT t1.oid, DATE_FORMAT(t2.date,'%d/%m/%y'),t2.site
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.oid = t2.fk_oid
WHERE
    t2.type = '-25'
    AND YEAR(t2.date) BETWEEN 2005 AND 2014
ORDER BY t2.date ASC
LIMIT 1;

Ps: I haven't tested the code.

Answer 2

SELECT  a.fk_oid AS oid,
        DATE_FORMAT(a.first_date, '%d/%m/%y') AS 'AVF Date',
        GROUP_CONCAT(b.site) AS "site(s)"
    FROM  
      ( SELECT  fk_oid,
                MIN(date) AS first_date
            FROM  site
            WHERE  date >  CURDATE() - INTERVAL 10 YEAR
              AND  date <= CURDATE()
              AND t2.type = -25
            GROUP BY  fk_oid 
      ) AS a
    JOIN  site AS b
    WHERE  b.fk_oid = a.fk_oid
      AND  b.date   = a.first_date ; 

• The inner query finds the first_date for each oid.
• The outer query gets the site, but further assumes there might have been multiple sites for a given oid and date.
• INDEX(type, date) is needed for performance
• INDEX(fk_oid, date) is needed for performance
• I assumed type is a number, not a string.
• I rewrote the date comparison to imply "in the last 10 years". If there are no rows for the future, get id of AND date <= CURDATE().
• Since you seem to need only the oid, I got rid of t1. If you need more fields from t1, then add JOIN t1 WHERE t1.oid = a.fk_oid and reference the fields needed.

(I am deleting my other Answer.)