From: Michael Widenius Date: April 18 1999 8:59pm Subject: RE: Performance. List-Archive: http://lists.mysql.com/mysql/2036 Message-Id: <14106.17420.277295.174323@monty.pp.sci.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! >>>>> "Marc" == =?iso-8859-1?Q?Marc De Caluw=E9?= 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. Regards, Monty