List:General Discussion« Previous MessageNext Message »
From:Philip Brown Date:October 5 2001 6:18pm
Subject:Bizarre query performance
View as plain text  
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?

Thread
Bizarre query performancePhilip Brown5 Oct
  • Re: Bizarre query performanceRussell Miller5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • Re: Bizarre query performanceRussell Miller5 Oct
        • RE: Bizarre query performancePhilip Brown5 Oct
          • Re: Bizarre query performanceRussell Miller6 Oct
            • RE: Bizarre query performancePhilip Brown6 Oct
  • Re: Bizarre query performanceBoyd Lynn Gerber5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • RE: Bizarre query performanceBoyd Lynn Gerber5 Oct
  • Re: Bizarre query performanceDan Nelson5 Oct
    • RE: Bizarre query performancePhilip Brown5 Oct
      • Re: Bizarre query performanceDan Nelson5 Oct
        • Re: Bizarre query performanceBoyd Lynn Gerber6 Oct
          • RE: Bizarre query performancePhilip Brown6 Oct
            • Re: Bizarre query performanceDan Nelson7 Oct
              • Preventing Nagle with mysql (was: Bizarre query performance)Philip Brown8 Oct
                • Re: Preventing Nagle with mysql (was: Bizarre query performance)Dan Nelson10 Oct
                  • Re: Preventing Nagle with mysql (was: Bizarre query performance)Sinisa Milivojevic10 Oct