Hi,
I have just started using mySQL (4.02) and have converted a Delphi program to use the libmySQL.dll. It is very slow to retrieve records.

I thought it was my programming so I carried out the following tests.
1) In windows98SE I started a DOS box and typed mysqld to start a server.
2) I started a client with mysql.
3) The query "Select * from songs" took 34 seconds to 4071 rows.

with the same server session running I started mySQLcc and typed the same query. This time 4071 rows were returned in 0.22 seconds.

Obviously something is not set up correctly. The tests suggest the problem is outside my Delphi code.

As a newbie I have no idea where to proceed from here.

Can anyone help?

Thanks

Ray

Hello,

I'm not sure, but I think that the difference in execution time between your two executions (within the same session) comes from the fact that once the query has been executed, it is buffered, making it very quick the second time you execute it. Since you only have 4000 records, taking 34 seconds suggerates that a full table scan is performed on the table (though that may not be the only problem). Do you have any index on your "songs" table ? If not, you should consider, for a "select*" query, to have a primary key constraint (and hence an index on that PK), so that the query engine can use this index to retrieve all rows efficiently. If you already have a PK, then the problem must come from somewhere else...

RBARAER

Thanks for the reply.

The table has a primary index on the first field (AlbumNumber).
If after testing the query in a dos box and the mySQLcc I then retype the query in the dox box it still takes 34 seconds to get 4000 rows. There seems to be a difference between the client connection that mySQLcc is using and the Dos box. The dos prompt (and my program) always take 34 seconds to get the records while mySQLcc always takes 0.22 seconds.

Reading the manual last night I see that there are connections through TCP/IP and connections through sockets (what ever they are). Could my program (and the Dos prompt) be connecting via TCP/IP while mySQLcc is using a socket?

Does anyone know how to check this? How do you specify what you want to use?

Ray

Hello,

Excuse me, but when you say that you execute the query from a dos box, is it by executing your program from the dos box ? If not, what do you mean by dos box (you must use a connection to the mysql server) ? Concerning TCP/IP and sockets, it seems odd to me that there were two such modes, for from a socket, you can choose to use several protocols such as TCP/IP or UDP, but the two seem independant to me (except if they speak of special sockets...). I think your program must use a TCP connection (through a socket). So, one thing you can do is disabling the TCP_NO_DELAY option when you connect to the server. Check if this is possible (this would force the query to be sent to the server immediately). This is a network connection improvement. Now, something else you can try in your program is to "prefetch" results in your resultset, and read several rows at a time instead of one (something like a next(30) compared to a next()) because that would minimize the number of calls to the server (for 4000 rows, you would do 134 calls instead of 4000) which would dramatically reduce network traffic. However, such optimizations are not possible though all APIs. I mainly work on Oracle with the OCCI and JDBC APIs. If you develop using JDBC, you can do this by using the setFetchSize(int rows) method. For now, I don't see another reason why you have such a problem.

I hope this will help you,

Regards,

RBARAER

I have found a solution to my problem.
I was sure that the problem was in the client side so I have been looking at what I could do when starting the client.
If I start the client with the command line "mysql -u root" then the select statement takes 34 seconds to return 4000 rows.
Last night I started the client with "mysql --compress -u root". The select statement took only 0.4 seconds to return the 4000 rows.
I logged in and out several times and each time it worked as above.
I'm not sure why turning on the server/client compression protocol (to quote the manual) should make such a huge difference.
I just need to take this back to my Delphi program and include the use of compression when I start the client. I'm hoping that this works as well as it does above.

Ray