Randomly select a MySQL record

This is a reminder to myself as I have this problem every now and then. I thought I share this with you.

Ok the problem is to random(ish)ly select a record from a MySQL database in an efficient way without any scripting or programming. There are several more or less simple ways described everywhere on the web, one being

SELECT * FROM table_name ORDER BY RAND() DESC LIMIT 1;

It does the job. Very slowly though, it can take MySQL several seconds or even minutes to complete the query. After fiddling around for some time, I came up with this:

set @randomId=FLOOR(RAND()*(SELECT MAX(pk_field) FROM table));
PREPARE randomStmt FROM "SELECT * FROM table WHERE pk_field > ? LIMIT 1";
EXECUTE randomStmt USING @randomId

… where pk_field is a numeric primary key. Ok the query goes like this:

  1. Get the highest value for pk_field from table_name, multiply with a random number between 0…1, cut off decimals and store in variable randomId.
  2. Prepare a statement where we can insert randomId and retrieve the desired record with.
  3. Take the query, insert randomId and execute.

This runs very fast even with a large dataset. I hear your objections already… :)

“This is stupid. Why didn’t you use COUNT(pk_field) to find the end of the number range? Even then this approach would still be stupid BTW.” Because it is slow. I currently have a table with >1.8 million records and COUNT(pk_field) takes >1 seconds to complete. Not good. MAX(pk_field) is good enough in most cases. YMMV though.

“The query is not truly random and prefers records at the end of a large pk_field range gap!” True. That’s what I meant with “YMMV” :) My solution is not truly random and it is biased, especially when there are large gaps in pk_field. It works for me though and is random enough when there are no large pk_field gaps.

Optimize WP-PostRating db table

Suffering from slow WP-PostRating plugin? Here’s how you could make it faster — add an index to the plugin’s database:

ALTER TABLE `wp`.`wp_ratings` ADD INDEX `Select1`(`rating_postid`, `rating_ip`);

Quick and painless. Replace wp.wp_ratings with the table name you are using.

It is BTW quite common for WordPress plugins that come with their own database tables not to create indexes. You may not notice that on a low traffic server but when using such plugins on high traffic servers such as Smashing Magazine you have to look out for them and take action.

MySQL macht “Peng”…

Heute hatte ich es wieder einmal mit einem abgeschossenen MySQL-Server zu tun, dieses Mal von einem Neukunden. Zur Abwechslung hat dieses Mal der Hoster (!) an der Maschine herumgespielt und sie ohne den Kunden zu informieren während des Betriebs neu gestartet — ohne die Dienste herunterzufahren.

Da fässt man sich nur noch an den Kopf. Das Resultat war natürlich klar — die Datenbank war kaputt.

Was tun… Einen Dump des aktuellen Stands der Datenbank ziehen ging nicht. Schreibzugriffe verhindern per LOCK ging nicht. Per MySQL reparieren ging auch nicht, denn die betroffenen Tabellen waren zum Teil InnoDB-Tabellen, die man lt. MySQL nicht reparieren kann. Ich habe dann den MySQL-Server neu installiert und vorher /var/lib/mysql aufgeräumt. Nun wirds interessant: Ein einige Stunden altes Backup, von dem ich annahm, dass es keine Fehler hatte, konnte ich zwar einspielen. Beim ersten Starten des MySQL-Daemons wurde kein Fehler angezeigt — bei den folgenden Neustarts aber schon: Fehlerhafte Tabellen…. WTF?

Das konnte nicht sein. Ein Problem mit dem Dateisystem konnte es auch nicht sein. Testweise habe ich die MySQL-Dateien auf eine andere Partition geschoben. Das Ergebnis war das gleiche. So langsam gingen mir die Ideen aus, wie ich das Problem schnell beheben konnte. Die nächste Idee: Wenn InnoDB-Tabellen nicht repariert werden können, nehmen wir eben MyISAM-Tabellen. Also den Dump entsprechend manipuliert und wieder eingespielt — voila. Ohne REPAIR wohlgemerkt.

Der Kunde war gerettet und der Abend gleich mit.