Hi!
I just forwarder your email about query caching (Yes, I know that the query cache in MySQL 4.0 has solved this for you but...) >> Hello all, >> >> I'm having real trouble trying to optimize MySQL cause I can't believe >> that >> MSSQL is faster. >> My configurations are as follows: >> MSSQL 2000 on W2K server. PIII 733 - 512 MB memory. >> MySQL-3.23.47-1 on Redhat 7.2. Dual PIII 1000 - 1.128 GB memory >> >> I have a PHP script that runs on a Redhat 7.1 - PIII 500 640 MB memory. >> The php script takes a username from a mysql table and runs a query for >> each >> of the usernames on another table. >> The test is that I have 2 different versions of the script that do the >> exactly same thing but one queries the MSSQL server and the other the >> MySQL. >> >> The MSSQL version takes 28 secs while the MySQL takes 34 secs. >> As you can see the MSSQL is much more slower with less RAM. >> >> I said what the heck I will use the my-huge.cnf to see if it makes any >> difference. >> Unfortunately nothing changed and then I started panicking.. It can't be >> true! >> >> I noticed that MSSQL caches the queries while MySQL doesn't. >> In my script I might have this: >> select emails from dbo_Company where username='1111'; >> come up 5 or even 10 times. >> >> If I run it on mysql It takes always 0.26 secs while it appears the >> MSSQL >> caches the result and doesn't take any time at all. Any chance you could give us a copy of the dbo_Company table to use for testing ? (If yes, please upload the .frm, .MYI and .MYD files to: ftp://support.mysql.com/pub/mysql/secret) According to tests I have done, for a simple query as the above, MySQL should usually be faster than MSSQL, even with MS-SQL query caching. (Look at: http://www.mysql.com/information/benchmark-results/result-db2,informix,ms-sql,mysql,solid,sybase-relative.html and the test for select_key, where MySQL is 3.52 times faster on simple key lookups) A couple of reasons for your results: - username is a primary key or the table is sorted according to the user name (explicite or implicit) in MS-SQL. - The query returns many rows, and they rows are far apart in the data file, so MySQL has to do many reads to fetch the rows. (In this case it's the file caching in Windows that is slow). In both cases, an OPTIMIZE TABLE or 'ALTER TABLE dbo_Company ORDER BY username' would speed up things considerably in MySQL. If the reason for the speed difference is that 'username' is a primary key and MS-SQL stores the row clustered together with the primary key, then by using the InnoDB table handler you should get the same benefit in MySQL as MS-SQL has. (The downside with this algorithm is that secondary keys are slower, but that is another issue) For example, MyISAM stores the rows separate from the keys. This makes the primary key slightly slower in MyISAM (except when you only want to have information from the primary key, then MyISAM should be faster), but instead all keys are equally fast and table scans are much faster. No sql server can be faster an ALL queries; Depending on the optimization engine and how rows are stored you get certain benefits but also some downsides. If MS-SQL is faster in this case, we would like to add a similar case to the MySQL benchmark suite because: - If MS-SQL is faster on this particular case, we want to acknowledge this fact (give credit where credit is due...) - We would like to know why MS-SQL is faster so that we can document this and provide workarounds - We will know about this and can in the future try to speed up MySQL for this case. - We will do the test with all the table handlers MySQL support; This will show if the speed of this test is dependent of how the rows are stored or by the optimizer. Thanks for any help you can give us regarding this! Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
