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" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com --------------------------------------------------------------------- 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
