4 ms·
Old story. Sorry if it's offtopic: I was running a internal webapp with a 25M rows in a MySQL table. The query for search was something like (pseudosql ahead):
by barredo 17y ago
Old story. Sorry if it's offtopic:
I was running a internal webapp with a 25M rows in a MySQL table. The query for search was something like (pseudosql ahead):
select * from table where match title_field against search_term order by id desc limit 20
It took around 10 to 12 seconds (it was a slow machine). When I removed the sorting statement in the SQL query (and so, the limit)
select * from table where match title_field against search_term
Then sorted the returned results with PHP and get the first 20. It took only 1 second.
- mrtron 17y agoWas the column you were ordering by indexed?
- hasanove 17y agoYou have probably done something wrong if you got figures like that or had a very specific edge case.
- rythie 17y agoThere won't be an index with both the full text search and the id in, so MySQL will have to create a temporary table with the results and sort that and do the limit. If you have a TEXT or a BLOB column or it's too big, MySQL will do that on disk rather than in memory, so it's slow. I'd suggest using "EXPLAIN EXTENDED" before your "SELECT" to see what it's doing and to consider specifying the columns, though in general I'd do what you did.
- ars 17y agoYou probably needed to run analyze table. What it probably did was sort by id (in memory), and then run the search (i.e. check each row if it matched, and stopping after 20 rows), not realizing that it should do the search first, and only then sort. EXPLAIN .... is your friend here. Without the order by, the only possible index was the search, so it used that. And digg probably had a similar error, since it makes no sense for php to be any faster in sorting than mysql.
- petercooper 17y agosince it makes no sense for php to be any faster in sorting than mysql. Perhaps MySQL has improved lately but working on my working knowledge of about 3 years ago, it can make sense to do sorting externally... if you can work with fewer data to do the sorting externally ;-) MySQL does/used to pull entire rows into the temporary table created for sorting. So if you have a 10,000 row, 100MB table and you're doing a SELECT that grabs, say, 10% (1000) of the rows and sorts them.. MySQL's temporary sort table will be 10MB (I found this to be true regardless of indexes - which only sped up the creation of the temporary table, but did not reduce its size - but as I was doing complex multi column sorts, it didn't matter anyway). If you do it in PHP/wherever, you can pull out ONLY the "id" and the column you want to sort by (say, a date), and use only about 1000 * 10 bytesish == 10KB of data to do the id sort. Then you pull the full rows out by id (which MySQL is quick at). Disclaimer: I last did all this about 3 years ago on an early version MySQL 5 deployment doing about 200 queries a second. So I ain't no RDBMS expert and what I say should probably be taken with a pinch of salt.
- ars 17y agoThis could work, but then you'd need to generate 1000 queries one at a time, meaning lots and lots of disk seeks. As long as your sort table stays in memory you are fine even if it's a lot of data, but if you hit the disk then you could be right. (But even if mysql says it hits the disk, doesn't mean it really does since the OS could cache it.) But there are better ways of handling this. Number one being an index on that column. You could also split the table in two, one part for sort data, the other for the large data. But I would only recommend that in very special cases, and only after careful benchmarking. Most of the time just increase the maximum size of in-memory temporary tables.