Have you tried "explain"ing the two select to see where all the time is
being spent and how the queries are optimized?
----- Original Message -----
From: "Philip Brown" <phil@stripped>
Sent: Friday, October 05, 2001 1:18 PM
Subject: Bizarre query performance
> 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
> so that I can time things exactly.
> 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
> calls on the client. I have been getting some very bizarre results, that
> 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
> 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
> across the network to the client. However, where the time is being spent
> the opposite for the 2 clients. Additionally, selecting a single unique
> 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
> 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 <mysql-thread87218@stripped>
> To unsubscribe, e-mail
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com