I'm trying to query a single table and retrieve the row which contains the earliest date in each of several groups. I understand this is somewhat difficult to understand, so here's an example of a query that works and does what I intend:
SELECT * FROM tbl_name
WHERE foo='foo_value'
AND date_field IN (
SELECT MIN(date_field)
FROM tbl_name
WHERE foo='foo_value'
AND bar IS NULL
GROUP BY group_col
)
AND (bar IS NULL)
GROUP BY group_col
ORDER BY group_col ASC;
Now, the problem is that when I run that query against my database it takes over 8 seconds.
Columns foo, date_field, and bar are all indexed. There are only about 1000 records in the table. If I run the subquery alone it takes .009 seconds and feeding those results statically to the primary query takes .008 seconds. 😕
I could separate them in my application, and I will if I must, but I'd like to understand what's causing this so that I can take advantage of subqueries (since I would think it should be faster, not slower, than doing each query separately).
Thanks in advance for any insight. 🙂
