You mention that you do a full SELECT of all the records. That is not normal operation of
any database. If you do SELECT statements that return reasonable amount of data which is
more the normal case, you will see that MySql is very fast. You also specified "Allow Big
results" on your queries. I personally change the "Temporary File Size" to allow large
results in server memory and the performance is very fast. The tests that I have done
with MSSQL Server and MySql have proved to be much faster in MySql than MSSQL both
inserting and selecting data.
Regards
Jose Ostos
----------
De: Aquiles Mejia[SMTP:aquiles.mejia@stripped]
Enviado: Viernes 19 de Noviembre de 1999 2:51 AM
Para: win32@stripped
Asunto: Performace is good doing a INSERT, but is bad doing SELECTs ( Queries ) !!!
Hi ,
I am making some test for decide wich Data Base we will include in our next proyect. I
wrote a program that INSERT/SELECT data into a data base, in a "similar" way the final
users will be doing that.
Here is the scenario:
(A) PC Client : Windows 98 , 64 MBytes RAM
(B) PC Server : Windows NT server, 64MBytes in RAM
Here are the results
CASE 1 : In a Client/Server Mode ( PC "A" vs PC "B" )
INSERT 10,000 records in all tables of the data base
MySQL 57.67 minutes aprox
InterBase 5.5 4 hours and 43 minutes aprox
MS SQL 7.0 13 hours and 5 minutes aprox
SELECT all records in the tables , with a relation Master -> Detail ( for example
Customers -> Orders )
MySQL 38 hours aprox
InterBase 14 hours aprox
MS SQL 7.0 10.19 hours aprox
CASE 2: In Local mode : Runing the Client /Server in the same machine , using the Windows
98 PC ( A )
INSERT 10,000 records in all tables of the data base
MySQL 31.1 minutes
InterBase 5.5 45.58 minutes
MS SQL 7.0 23.31 minutes
SELECT all records in the tables , with a relation Master -> Detail ( for example
Customers -> Orders )
MySQL 361.02 minutes aprox
InterBase 129.21 minutes
MS SQL 7.0 33.95 minutes
NOTE: The databases are defined in the same way and they have the same index files.
Does any body know why MySQL seems to have better performance on INSERT operations than
SELECT ? exist any special parameters for configure MySQL and improve the performance on
SELECT ?
I am using MySQL trial version, and just selecting "Allow Big results" on the Data Source
definition for my database. Also I am developing with Delphi 4.0 and accesing the Data
bases with the Borland Database Engine ( BDE ).
Thanks again for your help on this matter.
Aquiles.
Leonidas Aquiles Mejia Garcia
Desarrollo de Software
Computacion en Accion
e-mail: aquiles.mejia@stripped
Telefono: (3) 818-09-00 Ext 3321
URL: www.compac.com.mx
