>>>>> "Marc" == =?iso-8859-1?Q?Marc De Caluw=E9?= <iso-8859-1>
Marc> Thanks, I agree I should have read the manual better. The new query gives me
Marc> the same results in about 16 minutes.
Marc> Though I still keep sitting here with two big questions:
Marc> 1. In my eyes the two queries are quite similar. But the second one gives me
Marc> the results in 1/12th of the time! I would like to understand this. I tried
Marc> different things with EXPLAIN but I don't get an explanation for such
Marc> diffrent results. Can somenone explain how the optimizer exactly works?
To explain exaclty how the optimizer works would take a real long time
and probably not help you that much :(
Think instead of how you would solve the query by hand if the only
thing you can do are:
- Read trough all rows from a table
- Find a row quickly based on an key.
If you understand how you would do it, you are in a much better
position to tell how much work the SQL server must do.
You can find some information at:
- EXPLAIN syntax (Get information about a SELECT)
- Getting maximum performance from MySQL
The final documentation is of course the source code :)
(Sorry, but explaining exactly how the optimizer works would take a
little to much time for me just now; I couldn't probably do this in
less than 8 hours)
Marc> 2. I was looking for MySQL because I had some problems with the time Access
Marc> needed to join big tables. When I was looking at the performance sheets at
Marc> your site, MySQL seemed to be the solution. Although generally speaking I
Marc> really like MySQL, I'm just wondering if the perfomance on these big joins
Marc> is NOT on these sheets?
Note that there is many different types of joins; There are a lot
of big joins in the MySQL benchmarks, but it's impossible to cover all
possibilities in a test. Some joins these can be optimized to be very
fast, some can only be solved with much works. The MySQL optimizer can
optimize a quite broad range of queries, but not all (the optimizer is
something on could work on forever)
Marc> Anyone has an answer?
Without a copy of the tables to test with, its very hard to say what
could be done to get the query faster.