MYSQL query acting really, really slow [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

MYSQL query acting really, really slow [SOLVED]

Post by afbase »

My table contains about 1.3 gigs of data. It takes 25 minutes to return a simple query! Is there an easy way to fix this problem? Any help is appreciated :-Þ

Code: Select all

 
 SELECT ticker, timestamp FROM webcache GROUP BY ticker ORDER BY timestamp DESC LIMIT 0,20;
+--------+---------------------+
| ticker | timestamp           |
+--------+---------------------+
| HGM    | 2008-03-29 17:03:12 |
| TDI    | 2008-03-29 17:03:10 |
| XVG    | 2008-03-29 17:03:08 |
| ILB    | 2008-03-29 17:03:07 |
| CZJ    | 2008-03-29 17:03:05 |
| KVU    | 2008-03-29 17:03:03 |
| GJB    | 2008-03-29 17:03:01 |
| POH    | 2008-03-29 17:02:59 |
| INZ    | 2008-03-29 17:02:58 |
| XFD    | 2008-03-29 17:02:46 |
| JZH    | 2008-03-29 17:02:44 |
| FSE    | 2008-03-29 17:02:42 |
| UZG    | 2008-03-29 17:02:40 |
| HYJ    | 2008-03-29 17:02:34 |
| HJG    | 2008-03-29 17:02:32 |
| MNG    | 2008-03-29 17:02:23 |
| JZK    | 2008-03-29 17:02:21 |
| KAL    | 2008-03-29 17:02:19 |
| PLR    | 2008-03-29 17:02:17 |
| GED    | 2008-03-29 17:02:16 |
+--------+---------------------+
20 rows IN SET (25 min 14.41 sec)
 
Last edited by afbase on Mon Mar 31, 2008 3:07 pm, edited 1 time in total.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: MYSQL query acting really, really slow

Post by s.dot »

Do you have any indexes on your table fields?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Re: MYSQL query acting really, really slow

Post by afbase »

my index is the 'ticker' field, and my primary is another field 'ID' which is just autonumbered.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Re: MYSQL query acting really, really slow

Post by afbase »

Well.....I think i solved the problem for now. My query cache was set to 256 M's. Most of my tables change daily or every other day so this seems just silly to have. I reduced to the query cache to 0m and restarted the server. The query time for the same query was cut to 1 minute and 53 seconds. Is this still slow for 1.3 gb large table, with just 11 fields?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: MYSQL query acting really, really slow

Post by s.dot »

I'm not sure. I know I can query a 500 MB table with about the same amount of fields in just hundredths of a second. Try putting an index on `timestamp`.

Also, try EXPLAIN query_here, to see what mysql is telling you. Whether you can save some table scans or temporary tables or something.

This page may be very helpful: http://dev.mysql.com/doc/refman/5.0/en/ ... plain.html
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Re: MYSQL query acting really, really slow

Post by afbase »

The explain statement is shown below.

Code: Select all

EXPLAIN SELECT ticker, timestamp FROM webcache GROUP BY ticker ORDER BY t                                                                                                  imestamp DESC;
+----+-------------+----------+------+---------------+------+---------+------+--                                                                                                  ----+---------------------------------+
| id | select_type | TABLE    | type | possible_keys | KEY  | key_len | ref  | r                                                                                                  ows | Extra                           |
+----+-------------+----------+------+---------------+------+---------+------+--                                                                                                  ----+---------------------------------+
|  1 | SIMPLE      | webcache | ALL  | NULL          | NULL | NULL    | NULL | 6                                                                                                  502 | USING TEMPORARY; USING filesort |
+----+-------------+----------+------+---------------+------+---------+------+--                                                                                                  ----+---------------------------------+
1 row IN SET (0.00 sec)
 
I might as well state the types of fields i have. One mediumint(9), one varchar(6), 6 longtext fields (stores web pages), one timestamp, and two fields unused fields (for a future version of my php app), a medium text and a double.

The 6 longtext fields store the most of the data for each row. it still takes about two minutes to run the same query in my previous post... any other suggestions?
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: MYSQL query acting really, really slow

Post by Inkyskin »

As scottayy says, your best bet is to index the timestamp column. If it still could do with speeding up a little more after that, you could try indexing the ticker field too. Seriously, it can speed up a query like nobodies business.

Whats happening is that the database is trying to order by something it doesn't already know the order of - if you index it, mysql knows what order to put the rows in, and can work much much fatser.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: MYSQL query acting really, really slow

Post by s.dot »

If you put an index on timestamp, you should be able to get around the "extra: using filesort" shown in your EXPLAIN query.

According to the mysql manual:
Using filesort

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order. See Section 7.2.11, “ORDER BY Optimization”.
Here's the page to try to get around using filesort by optimizing (if you can.. probably can by indexing timestamp like mentioned above):
http://dev.mysql.com/doc/refman/5.0/en/ ... ation.html

You are also using a temporary table. MySQL says:
Using temporary

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
I'm not quite sure how to get around that one, but.. at last, mysql says this:
Extra

This column contains additional information about how MySQL resolves the query. The following list explains the values that can appear in this column. If you want to make your queries as fast as possible, you should look out for Extra values of Using filesort and Using temporary.
You are using both of those, so try to get rid of at least one.. hopefully both. :-D

All this info can be found on this page: http://dev.mysql.com/doc/refman/5.0/en/ ... plain.html
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: MYSQL query acting really, really slow

Post by Inkyskin »

Filesort is a bitch, its the single most common cause of slowdowns (In my experience). Thats why I index like it's going out of fashion! I'll take a larger database over a slower one any day.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

Re: MYSQL query acting really, really slow

Post by afbase »

Wow guys thanks!!!

Code: Select all

SELECT ticker,timestamp FROM webcache ORDER BY timestamp DESC LIMIT 0,20;                                                                                                  
+--------+---------------------+
| ticker | timestamp           |
+--------+---------------------+
| HGM    | 2008-03-29 17:03:12 |
| TDI    | 2008-03-29 17:03:10 |
| XVG    | 2008-03-29 17:03:08 |
| ILB    | 2008-03-29 17:03:07 |
| CZJ    | 2008-03-29 17:03:05 |
| KVU    | 2008-03-29 17:03:03 |
| GJB    | 2008-03-29 17:03:01 |
| POH    | 2008-03-29 17:02:59 |
| INZ    | 2008-03-29 17:02:58 |
| XFD    | 2008-03-29 17:02:46 |
| JZH    | 2008-03-29 17:02:44 |
| FSE    | 2008-03-29 17:02:42 |
| UZG    | 2008-03-29 17:02:40 |
| HYJ    | 2008-03-29 17:02:34 |
| HJG    | 2008-03-29 17:02:32 |
| MNG    | 2008-03-29 17:02:23 |
| JZK    | 2008-03-29 17:02:21 |
| KAL    | 2008-03-29 17:02:19 |
| PLR    | 2008-03-29 17:02:17 |
| GED    | 2008-03-29 17:02:16 |
+--------+---------------------+
20 rows in set (1.98 sec)
 
2 seconds is completely tolerable. I learned something today!!! :-Þ
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: MYSQL query acting really, really slow [SOLVED]

Post by s.dot »

2 seconds versus 25 minutes.
we have a winner! :)

You sped up your query about 7620%. Congrats!
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply