Hi everyone. I'm using MySQL 4.0.16 with W2K/SP4 on a 512KB RAM/1.2GHz
Athlon machine (my dev workstation).
I'm having a strange problem: when I execute a query via the MySQL Control
Center, it takes 10 seconds to return. About three seconds into the query,
I issue a "mysqladmin processlist" and see the State as "sending data". To
me, that means the query is done and the server is sending the data to the
client (I'm running both on same machine). The CPU is only at about 4%, but
the hard drive light is flashing like an XMas tree on crack.
I examined the query using "Explain" and it's using the correct index as I
specified.
If I re-execute the query, it returns in .13 seconds!!!
I thought maybe it was cached by the client so I kill the client and
execute it again - .13 seconds. I restart the server and do it again - .13
seconds. So it doesn't look like anything's being cached. Variables
query_cache_size=0 and query_cache_type=DEMAND, it seems like no caching
should happen. This happens to other queries as well.
I just don't get it. Why the difference?
The table "test" has 1,000,000 records, so I'm definitely not complaining
about a subsecond response - but if the public hits the production server
and I get the 10 second version for every query, I'm in trouble. Since
users dynamically create the queries, I can't count on them being cached -
so any moderately complex query seems to exibit this behavior. I really
feel like I need to understand why it's happening so I can fix any
underlying problems.
Any ideas????
Here's the query - because the users choose several options to build the
query, it's dynamically built via a Java class (don't think that's
relevant, but..):
SELECT
UID,UserName,Gender,City,State,Zip,Country,Age,Photo,OnLine,LastLogon FROM
test USE INDEX (big) WHERE Status=2 AND Viewable=1 AND Age >=18 AND Age <=
99 AND Photo!='' AND Height <=66 AND gender IN ('b') AND Weight >=100 AND
Height >=60 AND Weight <=150 LIMIT 250
The following columns are in the "big" multi-column index:
Status, Viewable, Online, Age, Height. See the table definition below.
here are the relavent columns:
+-------------------+---------------+------+-----+------------------------------+-------+
| Field | Type | Null | Key | Default
| Extra |
+-------------------+---------------+------+-----+------------------------------+-------+
| UID | int(11) | | PRI | 0
| |
| UserName | char(20) | | UNI |
| |
| Status | tinyint(1) | | MUL | 0
| |
| Viewable | tinyint(1) | | | 0 |
|
| City | char(30) | YES | | some city
| |
| State | char(2) | YES | | CA
| |
| Country | char(3) | YES | | USA
| |
| Zip | char(10) | YES | MUL | 90210
| |
| Age | tinyint(2) | YES | MUL | NULL
| |
| Gender | char(1) | YES | | NULL
| |
| Height | tinyint(3) | YES | | NULL |
|
| Weight | char(3) | YES | MUL | NULL
| |
| OnLine | tinyint(1) | YES | | NULL
| |
| Photo | char(30) | YES | | /path/path/photo.jpg |
|
+-------------------+---------------+------+-----+------------------------------+-------+
Thanks for any insight!
-Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
