NYCPHP Meetup

[nycphp-talk] Adding indexes

Adrian Noland anoland at indigente.net
Mon Mar 22 12:17:39 EDT 2010


In addition to what others said...

There is a lot more instruction both online and offline, but here is a brief
example illustrating EXPLAIN:

http://www.experts-exchange.com/articles/Database/MySQL/3-Ways-to-Speed-Up-MySQL.html

The docs can be a bit dense if you're not familiar with what you are looking
for, but here it is for reference:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

The first things you need to look at are the `type` and `ref`s columns. They
will tell you what, if any, keys are used. Another thing to look for is any
comments in the `Extra` column. If that has NULL, or 'filesort', or 'full
scan' then you have some work to do.

Finally,
I took a glance at the slides in this page and they look informative, but I
haven't watched the presentation. I will need to d/l it and watch it later.
http://forge.mysql.com/wiki/Optimizing_Queries_with_Explain



On Mon, Mar 22, 2010 at 10:27 AM, Rob Marscher
<rmarscher at beaffinitive.com>wrote:

> 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."' ";
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20100322/b1fc1c0f/attachment.html>


More information about the talk mailing list