Before anything else you should check you MySQL settings and adjust them. If you see disk I/O jump on your slow query, then MySQL may not be getting enough RAM and you should either adjust you settings file or add RAM. Ideally MySQL will have enough RAM to cache all the tables.
Are more rows returned on a slow query than a fast one? You don't have a LIMIT set in your query, are you really always going to use every row that is returned on every query? Are you using all the fields on every query? You have a "text" field which could contain lots of data. Remember, all of the data that results from the query must be read from the database and transferred to the calling process. If a query results in 10MB of data, MySQL may have the matching records in 1-2 seconds, but it's going to take a from seconds to transfer that much data over a network.
If this table is going to be huge (or already is) you may want to break off you text field into a separate table and then modify the current table to use char instead of varchar. This will result in fixed length records for your main query database and will speed things up. If you are worried about table locking, which I don't think is your problem, then you can switch to InnoDB table types.
On Sunday, July 27, 2003, at 12:00 AM, 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]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
