NYCPHP Meetup

[Rob Marscher]

On Mar 22, 2010, at 11:00 AM, Nicholas Hart wrote:
> I am looking to analyze and speed up some of my queries by adding any necessary indexes.  Is there a formula to follow in adding indexes to multiple join queries?  I have made some attempts using explain but am not sure I understand it all that well.
> 
> For example:
>     $sql = "select dt1.*, d.* from 
>         driver d join 
>         (select `driver`, date(`leave`), sum(`points`) as pnts, 
>         sum(`xpnts`) as xpnts from check_head 
>         where date(`leave`) = '".$sdate."'
>         group by `driver`) dt1 
>         on dt1.driver = d.id_num where `store` = '".$userStore."' ";

This looks like a pretty difficult query to tune.  There's a lot going on in your subselect - date functions, grouping, sum.  I'd skip the date() functions and do that conversion in php.  

Honestly, you're best bet is to denormalize this and have another table that stores exactly what you need (the points summed by driver and leave date and store).  Then you add indexes to leave date.  Every time you write to check_head, you also update the summary info in this new table.  When you select from it, you can query on just that table - no joins.  If you need more info on the drivers, gather up all of the unique driver ids in php, then issue another query with an "IN" in the where clause for all of the driver ids to get their info.  Then when you display to the user, iterate through the summary info and add in info about the drivers from your other query.  This way, you're only querying via indexes, not grouping or summing anything on the fly, and you're not even doing any joins.

You definitely need an index on driver.id_num if you don't have it already.  Going by the query as is, you need an index on check_head.driver too if you don't have it.  Mysql joins only perform decently if there are indexes on the columns you are using to join.

Good luck,
Rob