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:aqui...@compac.com.mx] Enviado: Viernes 19 de Noviembre de 1999 2:51 AM Para: win...@lists.mysql.com 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: aqui...@compac.com.mx Telefono: (3) 818-09-00 Ext 3321 URL: www.compac.com.mx