speeding up lookups on a table

[Bennett Haselton]

I have a MySQL query running inside a CGI script on my site that, at random 
intervals, seems to take 10-20 seconds to complete instead of less than 1 
second.  I spent so much time trying to track this down that I wrote a 
script which runs once a minute on the site, which (a) captures the output 
of "ps auwx" (listing all processes) so I can see if that has anything to 
do with the slowdown; (b) times how long it takes to run the query, and; 
(c) times how long it takes to run a similar query on a much smaller 
table.  (Part (c) is so that I can separate out whether it's the size of 
the table in part (b) that's making the difference, or the time taken to do 
something that's common to both queries, like getting a database handle.)

90% of the time, the large-table query takes less than 1 second, but 10% of 
the time, it takes 15-20 seconds.  (The small-table query always takes less 
than 1 second.)  I looked at the output of "ps auwx" to see if there seemed 
to be a relationship between the %CPU time used by other processes, or the 
number of other running processes, and the speed of the query, but there 
didn't seem to be.

So, my questions are:
(a) What is the usual cause of this type of problem?
(b) The query I'm running is:
SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id;
on a table whose description is:
+------------------------+------------------+------+-----+---------+----------------+
| Field                  | Type             | Null | Key | Default | 
Extra          |
+------------------------+------------------+------+-----+---------+----------------+
| ID                     | int(10) unsigned |      | PRI | NULL    | 
auto_increment |
| news_feed_owner_userid | int(10) unsigned | YES  | MUL | 
NULL    |                |
| URL                    | varchar(255)     | YES  |     | 
NULL    |                |
| title                  | varchar(255)     | YES  |     | 
NULL    |                |
| description            | text             | YES  |     | 
NULL    |                |
| date_and_time          | datetime         | YES  |     | 
NULL    |                |
| news_site_name         | varchar(255)     | YES  |     | 
NULL    |                |
+------------------------+------------------+------+-----+---------+----------------+

(if it wraps, widen message window to see it all).

Since I've already defined an index on news_feed_owner_userid, is there 
anything else I can do to make this kind of query run faster on this table?

	-Bennett

[EMAIL PROTECTED]     http://www.peacefire.org
(425) 497 9002
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]