Charles Engelke's Blog

July 9, 2003

OSCON Day 3: Session – The Anatomy of the MySQL Query Optimizer

Filed under: Ozette Brown — Charles Engelke @ 8:12 pm

Ok, the projector just bombed on us. And it’s taking way too long for them to get it fixed.
The session is informative, but a little slow. Oh, wait, the projector magically came back to life.
We have 11minutes left and there are many slides to go. Did I mention that Monty gave everyone
“Finlandia Vodka Chocolate”. When you bite into the cholocate, a burst of vodka comes out. I’m not
complaining.

Now, back to the session. Now we are looking at several queries and how to optimize them. At the very least,
any field after the “where” clause should be indexed. The lookups on indexed fields are quite fast and accurate.
There appears to be valid reason why you would use “FORCE INDEX” and “IGNORE INDEX” but they are starting to rush
through the slides. I’ll have to research that.
You should also use “EXPLAIN” to help optimize your query. EXPLAIN gives you detailed info about your query so you can
further optimize your query. It’s best to use the “slow query log” to trap what MySQL thinks are your slow queries.
Then, use EXPLAIN on those slow queries so you can find out what’s going on.

Once again, version 4.1 should be out in a couple of weeks according to Monty.

Todo:

  • add hash join method
  • index merge (select * from T where key1-const1 or key2=const2)
    – don’t use compound index where “or” is present

  • view rewriting
  • add merge joing

    This session could certainly be turned into another tutorial. Actually, at past conferences, I believe it was (or something close to it).
    The slides should help fill in the gaps. The slides aren’t available yet. Coming soon.

    Ok, were are done, se fin!!!

    Advertisements
  • Blog at WordPress.com.

    %d bloggers like this: