NYCPHP Meetup

[Rob Marscher]

On Mar 22, 2010, at 1:31 PM, John Campbell wrote:
...snip...
> 1:  add an index on the timestamp 'leave'
...snip...
> This is because mysql never uses indexes when a function is on the
> left hand side.
> 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.

One caveat to this is that mysql will only use one index per table in your query.  So it can either use the index on the foreign key for the join, or it can use the index on the column in the where clause.  Sometimes, by creating a multi-column index, it's able to use that index both for the join and the where clause... but it doesn't seem to work in all cases.  The query profiler can help you figure out what mysql is doing - http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html.  This gives you a lot more info than EXPLAIN.  With EXPLAIN, you can only check the key_len to try to figure out how much of your index is getting used.

By the way, you might want to make posts like this to the nyphp-mysql list because it seems to be watched by some experts in the mysql community.