Log in

No account? Create an account
brad's life [entries|archive|friends|userinfo]
Brad Fitzpatrick

[ website | bradfitz.com ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

use index [Feb. 6th, 2003|05:49 pm]
Brad Fitzpatrick
[Tags|, ]
[Current Mood |stupid & happy]

I learned a new MySQL trick today... USE INDEX (..)

MySQL's query optimizer sucks a dick. I'm not sure I'll ever write another query without explicitly specifying which index to use.

Why did I never see that before? I thought I had that manual memorized. :-(

[User Picture]From: lisa
2003-02-06 05:57 pm (UTC)
(Reply) (Thread)
[User Picture]From: krow
2003-02-06 06:03 pm (UTC)
I have bugged MySQL for a couple of years to document that. I believe there used to be a comment from me in the manual about it.

The bitch of the thing is that it doesn't always work. Even if you tell MySQL to use an index sometimes it won't. I have a couple of queries that every so often will just skip around to different indexes (which cause the frontpage to Slashdot to load slowly). I've shown the problem to Monty before but I don't think I ever got an answer from him about a solution.
(Reply) (Thread)
[User Picture]From: brad
2003-02-06 06:28 pm (UTC)
In MySQL 4.0.9 you can also use FORCE INDEX. This acts likes USE INDEX (key_list) but with the addition that a table scan is assumed to be VERY expensive. In other words a table scan will only be used if there is no way to use one of the given index to find rows in the table.

(Reply) (Parent) (Thread)
[User Picture]From: bandie528
2003-02-06 06:06 pm (UTC)
Eh. Sometimes it takes being stupid to learn, and be happy. Wait, duh. That whole sentence was stupid. But now I'm happy? ::shrug:: Seems I've taken on the role of Captain Obvious today. Oh well. There I go again. And again. I need to shut up. And there I am stating the obvious again!

Well, good for you! I'll shut up for everyone's sake now. :-D
(Reply) (Thread)
[User Picture]From: dakus
2003-02-06 06:17 pm (UTC)

hmmm? noticeable huh? cool!!
(Reply) (Thread)
(Deleted comment)
[User Picture]From: brad
2003-02-07 01:01 am (UTC)
Won't you be, my neighbor ???

(Reply) (Parent) (Thread)
[User Picture]From: whitaker
2003-02-06 08:59 pm (UTC)
I'm really glad I read this. Hard ass.
(Reply) (Thread)
[User Picture]From: mart
2003-02-07 12:52 am (UTC)

After having people tell me for about four years that “MySQL's query optimiser will sort it out”, this is quite a surprise.

Oh well.

(Reply) (Thread)
[User Picture]From: brad
2003-02-07 01:02 am (UTC)
It works well for constants, but not for ranges, from what I can tell.

(Reply) (Parent) (Thread)