3 ms·
since 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
by petercooper 17y ago
since 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.
- petercooper 17y agoThis could work, but then you'd need to generate 1000 queries one at a time, meaning lots and lots of disk seeks. Not with a SELECT .. WHERE id IN (x,x,x,...) type query. But, yeah, sort of. I actually did trial with separate SELECTs for every ID at first and it performed surprisingly well. As long as your sort table stays in memory you are fine even if it's a lot of data Trueish. At the time, though, it was a 10-12GB database on a 4GB machine and in 2006 I didn't have the cashflow to get anything better. So the most popular parts of the DB were cached OK, but there was little left for temporary tables. I say "trueish", though, because you're still using more memory bandwidth with temporary tables in this situation and that's an issue sometimes. Number one being an index on that column. The problem was that there were about 8 metadata columns and sorts were done with varying combinations of these (though usually one). Indexes became a big problem at a certain point because of how difficult they made it to clear stale data from the database (at one point we were doing 6 hour maintenance periods each month - with caches picking up the slack - to delete old records). If I were doing it again, I'd do a lot differently (and not use MySQL), of course.. so your suggestions are certainly useful to anyone tackling a similar problem with 2010 eyes.
- jseifer 17y agoWhat would you use instead of MySQL?
- petercooper 17y agoMongoDB is a running joke with you but, seriously, probably MongoDB along with Redis. It wouldn't be using the same algorithms as it did on MySQL, though. There are better ways to structure that sort of data nowadays.
- jseifer 17y agoIt's funny that it comes off as a joke since I really do love it. I mean, it powers http://nyhtp.com http://nyhtp.com. I've had nothing but great experience with Redis, mixed experience with tokyo cabinet and tyrant, and not enough real work experience with Mongo.
- petercooper 17y agoMongoDB + Redis are proving a winning combination for me so far. Or, rather, the combination of having a fast key/value store with ephemeral key support and a document database is a winner. It gives you two levels and more immediate optimization and separate of concerns opportunities.