CODEWITHSUNDEEP
mysqlperformance
SOfanatic
SOfanatic

Reputation: 5573

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 the following:

SELECT * 
FROM 
     table1
LEFT OUTER JOIN 
     table2 ON table1.id = table2.fkId
LEFT OUTER JOIN 
     table3 ON table1.id = table3.fkId
GROUP BY 
     table1.id
HAVING 
     table2.date_updated = max(table2.date_updated)
     AND table3.date_updated = max(table3.date_updated)

My output looks like this:

 name |   table2   |  table3  
joe   | 2013-04-01 | 2013-01-31
peter | 2013-02-04 | 2013-01-02
sandra|            | 2013-04-01

I get the data I need but this query takes too long, is there anyway to optimize it without modifying the table indices?

Things to point out:

EDIT **

Query is taking around 3-4 secs when using a WHERE table1.id = id , to return a single record.

Table1 has ~ 84000 recs

Table2 has ~ 96000 recs

Table3 has ~ 81000 recs

Upvotes: 0

Views: 94

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

With your data as presented, the query is seems to be:

SELECT table1.name, MAX(table2.date_updated), MAX(table3.date_updated)
FROM  table1 LEFT OUTER JOIN 
      table2
      ON table1.id = table2.fkId LEFT OUTER JOIN 
      table3 ON table1.id = table3.fkId
GROUP BY table1.id

Indexes on table2(fkid, date_updated) and table3(fkid, date_updated)` might help.

Actually, with such indexes, this version will probably have better performance:

select table1.name,
       (select date_updated from table2 where table1.id = table2.fkid order by date_updated desc limit 1
       ) as T2,
       (select date_updated from table3 where table1.id = table3.fkid order by date_updated desc limit 1
       ) as T3
from table1

This eliminates the grouping entirely, replacing it with a correlated subquery -- and the correlated subquery should turn into an index small index scan.

Upvotes: 1

DRapp
DRapp

Reputation: 48139

I know you mentioned without modifying the indexes, but what you are trying to do would only be more optimized if you had each "Table2" and "Table3" to have indexes on

( fkId, date_updated ).

If you only have an index on each table's "ID" column, then obviously nothing will be optimized on the join. You would need it on the foreign key to Table1 at a minimum. But since this would be a NEW index on the table, it should not hurt anything, but only HELP your queries. Do this index on BOTH Table2 and Table3.

Upvotes: 0

Related Questions