List:General Discussion« Previous MessageNext Message »
From:Russell Miller Date:October 5 2001 6:14pm
Subject:Re: Bizarre query performance
View as plain text  
Have you tried "explain"ing the two select to see where all the time is
being spent and how the queries are optimized?

--Russell

----- Original Message -----
From: "Philip Brown" <phil@stripped>
To: <mysql@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
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 <mysql-thread87218@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-duskglow2000=yahoo.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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