Page 1 of 1
MYSQL query acting really, really slow [SOLVED]
Posted: Sun Mar 30, 2008 2:23 am
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)
Re: MYSQL query acting really, really slow
Posted: Sun Mar 30, 2008 2:26 am
by s.dot
Do you have any indexes on your table fields?
Re: MYSQL query acting really, really slow
Posted: Sun Mar 30, 2008 2:45 am
by afbase
my index is the 'ticker' field, and my primary is another field 'ID' which is just autonumbered.
Re: MYSQL query acting really, really slow
Posted: Sun Mar 30, 2008 2:53 am
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?
Re: MYSQL query acting really, really slow
Posted: Sun Mar 30, 2008 3:04 am
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
Re: MYSQL query acting really, really slow
Posted: Mon Mar 31, 2008 2:28 am
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?
Re: MYSQL query acting really, really slow
Posted: Mon Mar 31, 2008 4:03 am
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.
Re: MYSQL query acting really, really slow
Posted: Mon Mar 31, 2008 5:03 am
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.
All this info can be found on this page:
http://dev.mysql.com/doc/refman/5.0/en/ ... plain.html
Re: MYSQL query acting really, really slow
Posted: Mon Mar 31, 2008 6:32 am
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.
Re: MYSQL query acting really, really slow
Posted: Mon Mar 31, 2008 1:30 pm
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!!! :-Þ
Re: MYSQL query acting really, really slow [SOLVED]
Posted: Tue Apr 01, 2008 4:46 am
by s.dot
2 seconds versus 25 minutes.
we have a winner!
You sped up your query about 7620%. Congrats!