network profile
| bio | website | jamestitcumb.com |
|---|---|---|
| location | England, United Kingdom | |
| age | 26 | |
| visits | member for | 7 months |
| seen | Oct 26 '12 at 19:07 | |
| stats | profile views | 1 |
|
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 |
