mysql - is there a way to optimize this sql server query? -
i have 3 tables:
table1:
id | name 1 | joe 2 | peter 3 | sandra table2:
id | fkid | date_updated 1 | 1 | 2013-01-31 2 | 1 | 2013-04-01 3 | 2 | 2013-02-04 4 | 2 | 2013-01-02 table3:
id | fkid | date_updated 1 | 1 | 2013-01-31 2 | 3 | 2013-04-01 3 | 3 | 2013-02-04 4 | 2 | 2013-01-02 i have following:
select * table1 left outer join table2 on table1.id = table2.fkid left outer join table3 on table1.id = table3.fkid group table1.id having table2.date_updated = max(table2.date_updated) , table3.date_updated = max(table3.date_updated) my output looks this:
name | table2 | table3 joe | 2013-04-01 | 2013-01-31 peter | 2013-02-04 | 2013-01-02 sandra| | 2013-04-01 i data need query takes long, there anyway optimize without modifying table indices?
things point out:
table2 , table3 not same table.
i need entire row "last_updated" table2 , table3, not date.
edit **
query taking around 3-4 secs when using where table1.id = id , return single record.
table1 has ~ 84000 recs
table2 has ~ 96000 recs
table3 has ~ 81000 recs
with data presented, query seems be:
select table1.name, max(table2.date_updated), max(table3.date_updated) table1 left outer join table2 on table1.id = table2.fkid left outer join table3 on table1.id = table3.fkid group table1.id indexes on table2(fkid, date_updated) , table3(fkid, date_updated)` might help.
actually, such indexes, version have better performance:
select table1.name, (select date_updated table2 table1.id = table2.fkid order date_updated desc limit 1 ) t2, (select date_updated table3 table1.id = table3.fkid order date_updated desc limit 1 ) t3 table1 this eliminates grouping entirely, replacing correlated subquery -- , correlated subquery should turn index small index scan.
Comments
Post a Comment