Explain this EXPLAIN

vitalyb

1) In the picture there are two SQL queries that I ran. The only diffence between them is the OR bv_bookgenres.genre_id = 1. Yet when I add the followign OR it can't use the index in the second table. Can someone explain me these results?

2) Is there some document/book explaining the mySQL explain?

ultraslacker

http://www.mysql.com/doc/en/MySQL_indexes.html

No indexes will be used with OR.

vitalyb

Errr.. I started to suspect as much 🙁.

Why's that? I mean... They don't happen to mention this fact in their documentation... That's plain weird 😕.

Sxooter

The basic problem is that MySQL uses a dirt simple rules based query planner. It's fine for 99% of all storage / retrieval systems that are doing simple stuff, but falls over with a case of the stupids for complex queries.

you might wanna look at postgresql if you're gonna be doing complex queries a lot. It's got a much smarter query planner that's cost based. It does, however, require a certain amount of tuning before it starts making the right decisions on a given piece of hardware.

melody

Yeah I'll second that- postgres is the dogs danglies if you've got access to it on your server. It scales up a lot better too so if you're using a large database it'll wipe the floor with mySQL.

😃

On the subject of using EXPLAIN, as has been mentioned before the postgres plan optimizer shows how much each stage 'costs' in terms of performance so you can see the point at which queries bottleneck. That's been very useful. It also can use indexes with OR statements. Not sure what the solution to that would be using MySQL - have you tried rewriting the query using subselects or joins?

vitalyb

PostgreSQL has other troubles... First of all my testing OS is Windows and so I can't experience the database true speed that way.

Also, I keep finding documents of comprasions between MySQL and PostgreSQL proving how slow it is (like this one) which quite discourage me from the whole postgresql. Add to that the not very high amount of hosts allowing postgresql... Well.. You get the point.

P.S It is very possible that the features given by postgresql overcome its speed in these documents. I can't really know that... I am discouraged by them anyhow. Moving data back and forth is quite a mess.

ultraslacker

That I think postgresql is superior to mysql, and specifically that after using both for the last three years I can say that I prefer postgresql's robustness, acid compliance, features, and performance to mysql is of course not relevant to this thread. See other threads for more insightful posts comparing mysql with a current version of postgresql (7.1.3 is ancient, btw). You are stuck with what best fits your constraints and requirements. You could pester the host to upgrade mysql as I believe 4.x has better heuristics. It may even use an index or two for that query.

Far more interesting though is why you are forced to use such an awkward join. Perhaps it would work better if you used two instances of bv_bookgenres

vitalyb

It was just an example... I solved it eventually using another way, I just wanted to know what was wrong 🙂.

Sxooter

Originally posted by vitalyb
PostgreSQL has other troubles... First of all my testing OS is Windows and so I can't experience the database true speed that way.

Yep, if you're on windows, your choices ala postgresql are pretty limited right now. You can:
Wait for the windows port that should be in 7.5 in the spring. OR
Install a windows port that is free but unsupported (there are several, but most are 7.2.1 or older) OR
Run cygwin, which is SLOW. OR
Setup a linux box OR
Use VMWare, which is faster than cygwin, but not as fast as linux. OR
Try out firebird / interbase, which runs very well on windows.

Also, I keep finding documents of comprasions between MySQL and PostgreSQL proving how slow it is (like this one) which quite discourage me from the whole postgresql. Add to that the not very high amount of hosts allowing postgresql... Well.. You get the point.

Sorry, but that comparison is so very lopsided. Try a comparison that matters, like how well each database runs when there are 1000 readers, and 100 writers to your database. How fast a database runs for a single user is basically unimportant as long as it's "fast enough", which most of them are nowadays. Note that SQLLite can only run that fast for one single person. It has massive locking / performance issues, which means it will let you build a site that is lightening fast in single user testing, but implodes under heavy parallel access.

P.S It is very possible that the features given by postgresql overcome its speed in these documents. I can't really know that... I am discouraged by them anyhow. Moving data back and forth is quite a mess.

What is far more possible is that the benchmarks you are looking at are meaningless for comparisons in the real world, where hundreds or thousands of people hit real databases at the same time. I can run 200 to 600 transactions per second on commodity hardware with a parallel load of 50 writers and hundreds of readers in Postgresql. Neither MySQL nor SQL lite can handle that kind of load and maintain transactional integrity.

Postgresql fills a different design / problem space then either MySQL or SQLLite. They are perfect for workgroup size applications that are mostly read and have little problems with contention. Postgresql is what you use for larger groups with heavy contention. Oracle is what you use for very heavy parallel load with failover, point in time recovery, etc...

If a database benchmark doesn't run multiple threads doing the same basic things (i.e. creating contention) it doesn't mean squat, because you're gonna eventually need to handle heavy parallel access as an application gets more and more use. Finding out that your database choice results in collapses under load in production is bad. find out in the testing phase.