I have been testing an application that uses mysql on SCO OpenServer and I have discovered some strange query performance. To investigate the matter further, I have written a client program that uses the mysql C API directly so that I can time things exactly.
Environment: Server: SCO OpenServer V3.2 R5.0.5, AMD K6-2 350Mhz CPU, 128Mb RAM mySQL: 3.23.39, compiled by me to avoid use of libraries, using latest available pthreads Clients: Win32 machines (more detail later). There are 2 times I am interested in, the time to execute a query, and the time required to fetch the results across the network (100Mbps LAN, 3 isolated workstations in test setup). Basically I have been timing the mysql_query() and the mysql_store_result() calls on the client. I have been getting some very bizarre results, that are 100% reproducible: QUERY 1: SELECT * FROM X WHERE ID=100 ID is defined as the PRIMARY KEY for this table, therefore this query returns exactly one row, and should be very quick. This table only has around 20 columns, mostly integers, no long text fields. Client 1 (Windows 2000 SP2, AMD Athlon 900Mhz processor, 512Mb RAM) mysql_query takes less than 10ms to execute. This is fine. mysql_store_result takes around 130ms-200ms to execute. This is not fine! The performance of this query is the same when run against a number of tables (all of my tables have a column called ID defined as a PRIMARY KEY). Client 2 (Windows 98SE, Intel Pentium II 400Mhz, 256Mb RAM) mysql_query takes around 180ms to execute. mysql_store_result takes less than 10ms to execute. Analysis: both clients take about 200ms to execute the query and fetch data across the network to the client. However, where the time is being spent is the opposite for the 2 clients. Additionally, selecting a single unique row using a primary key should not take 200ms. Investigating this further, I tried the following query on the same table: QUERY 2: SELECT * FROM X WHERE SCHEDULEDSTART BETWEEN "2001-10-06" AND "2001-10-07" This query returns 33 rows, i.e. a lot more data than the previous query! SCHEDULEDSTART is an indexed DATETIME field. mysql_query takes around 10ms to execute on both clients. mysql_store_result takes no measurable time to execute. These results are completely reproducible and make no sense! Fetching a single row using a primary key takes around 200ms, while fetching 33 rows using another indexed field takes around 10ms! As I say, I can reproduce this entirely. Can anyone give me some assistance with this bizarre behaviour? --------------------------------------------------------------------- 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
