28 reputation
3
bio website jamestitcumb.com
location England, United Kingdom
age 28
visits member for 1 year, 9 months
seen Jul 26 at 14:42

Oct
26
comment Fetch the newest row grouped by a column in MySQL
As I described in the comments in @RolandoMySQLDBA's answer, adding a compound key didn't make any difference. The issue in the end was the huge amount of data in the TEXT field in the comment column meaning there was too much disk seeking going on. I preferred the subquery to using a GROUP_CONCAT hack, that's all.
Oct
25
comment Fetch the newest row grouped by a column in MySQL
Thanks - a different answer that also worked, but was just as slow as the other.. it seems a bit hackier too, but nice thinking :)
Oct
25
awarded  Scholar
Oct
25
comment Fetch the newest row grouped by a column in MySQL
The issues stem from the comment field being a TEXT type. I imagine this causes a large amount of disk seek type in such a large table (1.3 million rows), so I have done this a different way. I have accepted your answer, thank you for your time!
Oct
25
accepted Fetch the newest row grouped by a column in MySQL
Oct
24
awarded  Supporter
Oct
24
comment Fetch the newest row grouped by a column in MySQL
@RolandoMySQLDBA That does improve it slightly, but running only the subquery (i.e. SELECT entity_id, MAX(`date`) last_log_date FROM log_table GROUP BY entity_id) is taking 8 seconds on its own. What I asked in my last comment is if there is a way to vastly increase the performance of just that query - that would most likely solve all the issues...
Oct
23
comment Fetch the newest row grouped by a column in MySQL
@matts As per @RolandoMySQLDBA's edited answer I added a compound index to the columns and this didn't make any difference. Running just the subquery itself (i.e. the SELECT entity_id,MAX(last_log_date) last_log_date FROM log_table GROUP BY entity_id part) and that is what is taking the time to run (it still takes 11 seconds). It seems to me that this isn't going to get any quicker?
Oct
23
comment Fetch the newest row grouped by a column in MySQL
Ok this query seems to be more efficient. On a table with 87,283 distinct entity_id and 1,309,252 records, my query takes too long (more than 50s until I killed the query) but your query takes just over 11 seconds. Is there a way to speed this up further? I have indexes on the entity_id and the date columns already. Ideally the query should run more or less instantly if possible...
Oct
23
awarded  Student
Oct
23
asked Fetch the newest row grouped by a column in MySQL