NYCPHP Meetup

[John Campbell]

On Mon, Mar 22, 2010 at 11:00 AM, Nicholas Hart
<nhart at partsauthority.com> wrote:
> 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."' ";

Below are a couple of optimizations:
1:  add an index on the timestamp 'leave', and then change
WHERE DATE('leave') = $sdate
to:
WHERE leave > $sdate AND leave < ($sdate + INTERVAL 1 DAY);

This is because mysql never uses indexes when a function is on the
left hand side.

2. Do you really need the subquery?  I mean why not just use an inner join?
The query can probably be re-written as:

SELECT d.*, DATE(), SUM(), ...
FROM driver
JOIN checkhead dt ON dt.driver = d.id_num
WHERE dt.leave > $sdate ...
GROUP BY dt.driver

The fastest query plan is to filter the check_head to the day in
question, and then do the joins/groups on the small subset of records.
 You have to trick mysql into executing it that way.  If you have 3
years of data, and you should see a 1000x speedup by doing it my way.

There is no "formula" to follow, but you need to intuitively
understand how relational databases work to write fast queries.  As a
starting point, 1. index foreign keys
2. no formulas on the left side of a where statement
3. create indexes for columns in the where clause.

Regards,
John Campbell