Monday, October 3, 2011

select a field by first row via left join

There are times when we need to select first record of a table in a join tables.
Problem with mysql is that after joining tables together, there will be no indexing,
making query extremely slow.

First, makesure all inner join uses on query to limit the records instead of where condition.
This way, the record is filtered based on table indexes (make sure you index'em first) in the joining process.

2nd, to only populate a field by first row which matches the criteria, do a left join

select x.field1, x.field2 from table1 x inner join table2 x2 on x.field3=x2.field2
left join table2 x3 on x2.field1=x3.field1 and x3.fields=[some condition to filter to first row]
group by ...

No comments: