Erin ONeill wrote:
> why did they use ORs when MySQL does not
> use indexes with ORs?? ok, rant mode off.
Because, quite simply, there's an awful lot of people out there writing poor software. It is a constant source of frustration for me, as the reputation of our industry continues to degrade as each poorly constructed application hits the "free airwaves", gets downloaded, and the mess is copied from one server to the next willy nilly. Glad to see you're actually combatting this though; the best thing you could do is notify the developers of the application that they should make some changes. Provide evidence of your performance problems and tell them to pick up a good book on MySQL (flagrant plug...)
> here's what the explain said about the table with
> BOTH the indexes
> ...
> It looks like it's better? no?
Well, in order to determine if the query is performing at an optimal level, you should first try the query for an email address which you know is actually in the tables, and check that the indexes are used appropriately.
Let me know how things turn out. Good for you for tracking these issues down and good job investigating so far!
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com