MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 18 1999 8:59pm
Subject:RE: Performance.
View as plain text  

>>>>> "Marc" == =?iso-8859-1?Q?Marc De Caluw=E9?= <iso-8859-1>
> writes:

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.

Performance.Marc De Caluwé9 Apr
Re: Performance.Fred Lindberg9 Apr
  • RE: Performance.Marc De Caluwé13 Apr
    • Re: Performance.Sasha Pachev13 Apr
RE: Performance.Marc De Caluwé18 Apr
  • RE: Performance.Michael Widenius19 Apr