On Mon, Mar 22, 2010 at 11:00 AM, Nicholas Hart
<nh...@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
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
