This could be caused by table locking. If another Mysql process ha a lock on the table then other processes start to queue up. Maybe this query is getting stuck behind other queries or a single slow query.
Just a thought. -Jackson On Saturday 26 July 2003 11:00, Bennett Haselton wrote: > 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]
