1

I am trying to extract a random article who has a picture from a database.

SELECT FLOOR(MAX(id) * RAND()) FROM `table` WHERE `picture` IS NOT NULL

My table is 33 MB big and has 1,006,394 articles but just 816 with pictures. My problem is this query takes 0.4640 sek

I need this to be much much more faster. Any idea is welcome.

P.S. 1. of course I have a index on id. 2. there is no index on the picture field. should I add one? 3. the product name is unique, also the product number, but thats out of question.

RESULT OF TESTING SESSION.

@cHao's Solution is faster when I use it to select one of the random entries with a picture.(les then 0.1 sec. But its slower if I try to do the opposite, to select a random article without picture. 2..3 sec.

@Kickstart's Solution is a bit slower when trying to find a entry with picture, but is almost same speed when trying to find a entry without picture. average 0,149 sec.

@bob-kruithof's Solution don't work for me. when trying to find a entry with picture, it selects a entry without picture.

and @ganesh-bora, yes you are right, in my case the speed difference is about 5..15 times.

I want to thank you all for your help, and I decided for @Kickstart.

  • are you even getting an article with this? it seems like you're selecting a number, and not a random article. the where should be id = rand() (which is completely not optimized, but start from there) – Itai Sagi May 09 '13 at 08:42
  • 1
    Do you have an index on the column `picture`? – Kickstart May 09 '13 at 08:44
  • 2
    the trick with `rand()` only works, as far as I know, for more-or less continues id's. What you haven't shown here, but is crucial, is the `select where id <= "yourrandid"`, for this to work, but I assume it is there somewhere?. Anyway, if it is just 816 pictures, couldn't you just retrieve those 816 as fast as you can, and then select a random one in code? To select the picture you would need an index on that column... – Nanne May 09 '13 at 08:50
  • @Nanne there are ways to deal with the id's with gaps. Also, making your application do the selecting out of a heap of results from a query is very inefficient, not to mention even unnecessary when using the right query. – halfpastfour.am May 09 '13 at 09:08
  • How many time takes to execute "SELECT id FROM table WHERE picture IS NOT NULL"? – mauretto May 09 '13 at 09:10
  • @bob: I know there are but the more gaps you have (and this is more gaps then data), the less 'random' it becomes, making the whole excersize futile. Seeing that there are only 816 pictures, I don't think we need to worry about that. This is a marginal amount of id's, so there is absolutely no reason to go through hoops. the issue here is the BIG database, with the SMALL subset of id's we're interested here. Not the fact that random choice is an issue. – Nanne May 10 '13 at 08:13
  • Thank you for the feed back on timings, etc. If you have the time it could be interesting to see EXPLAINs of the selects for comparison. – Kickstart May 10 '13 at 08:17

4 Answers4

1

You might try attaching a random number to each row, then sorting by that. The row with the lowest number will be at the top.

SELECT `table`.`id`, RAND() as `order`
FROM `table`
WHERE `picture` IS NOT NULL
ORDER BY `order`
LIMIT 1;

This is of course slower than just magicking up an ID with RAND(), but (1) it'll always give you a valid ID (as long as there's a record with a non-null picture field in the table, anyway), and (2) the WTF ratio is pretty low; most people can tell what's going on here. :) Its performance rivals Kickstart's solution with a decently indexed table, when the number of items to select from is relatively small (around 1%). Definitely don't try to select from a whole huge table like this; limit it first with a WHERE clause on some indexed field(s).

Performancewise, if you have a long-running app (ie: not PHP; i'm talking about Java, .net, etc where the app is alive even between requests), you might try to keep a list of all the IDs of items with pictures, select a random ID from that list, and load the article. You could do that in PHP too, if you wanted. It might not work as well when you have to query all the IDs each time, but it could be very useful if you can cache the list of IDs in APC or something.

Community
  • 1
  • 1
cHao
  • 84,970
  • 20
  • 145
  • 172
  • 1
    I suspect this would be slower than the basic concept of @eichertc existing code (although the original code would seem likely to return a non existant id which yours will always return a useful id). – Kickstart May 09 '13 at 08:51
  • @Kickstart: It is slower, sort of. But only til you take into account that just magicking up an ID with `RAND() * MAX(id)`, you'd have to keep doing it til you get a valid one. :) – cHao May 09 '13 at 08:53
  • There are ways around that (and they make my brain hurt first thing in the morning....) – Kickstart May 09 '13 at 08:56
  • not a bad idea with the array of ids. the flaw is that some other scripts update and delete pictures. –  May 11 '13 at 21:27
  • @eichertc: Yeah, you'd have to refresh the list every once in a while. How often depends on how much you care about items with pictures being available immediately. Or, in some cases, you could have insert/delete triggers that update a timestamp somewhere -- and you check that timestamp to see whether you have to refresh the list. – cHao May 12 '13 at 00:28
1

You need to get a range of values with matching records and then find a matching record within that range.

Something like this:-

SELECT r1.id
FROM `table` AS r1 
INNER JOIN (
    SELECT RAND( ) * ( MAX( id ) - MIN( id ) ) + MIN( id ) AS id
    FROM `table`
    WHERE `picture` IS NOT NULL
) AS r2
ON r1.id >= r2.id
WHERE `picture` IS NOT NULL
ORDER BY r1.id ASC
LIMIT 1

However for any hope of efficiency you need an index on the field it is checking (ie, picture in your example)

Just an explanation of how this works.

The sub select finds a random id from the table which is between the min and max ids for records for a picture. This random id may or may not be for a picture.

The resulting id from this sub select is joined back against the main table, but using >= and with a WHERE clause specifying that the record is a picture record. Hence it joins against all picture records where the id is greater than or equal to the random id. The highest random id will be the one for the picture record with the highest id, so it will always find a record (if there are any picture records). The ORDER BY / LIMIT is then used to bring back that single id.

Note that there is an obvious flaw to this, but most of the time it will be irrelevant. The record retrieved may not be entirely random. The picture with the lowest id is unlikely to be returned (will only be returned if the RAND() returns exactly 0), but if this is important this is easy enough to fix by rounding the resulting random id. The other flaw is that if the ids are not vaguely equally distributed in the full range of ids then some will be returned more often than others. For example, take the situation where the first 1000 ids were pictures, then no more until the last (33 millionth) record. The random id could be any of those 33 million, but unless it is less than or equal to 1000 then it will be the 33 millionth record that will be returned.

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Joins are usually cheap and fast, if the items being joined are doing so on keyed columns. An index on picture is the most important thing though as without that it is having to scan through 33m records to find which ones it is interested in. – Kickstart May 09 '13 at 14:04
0

for performance you can first add index on picture column so 814 records get sorted out at the top while executing the query and then you can fire your query.

Ganesh Bora
  • 1,133
  • 9
  • 17
0

How has someone else solved the problem?

I would suggest looking at the this article about different possible ways of selecting random rows in mysql.

Modified example from the article

SELECT name
FROM random JOIN
    ( SELECT CEIL( RAND() * (
        SELECT MAX( id ) FROM random WHERE picture IS NOT NULL
    ) ) AS id ) AS r2 USING ( id );

This might work in your case.

Efficiency

  • As user Kickstart mentioned: Do you have an index on the column picture? This might help getting you the results a bit faster.
  • Are your tables optimized?
halfpastfour.am
  • 5,764
  • 3
  • 44
  • 61
  • this solution is not working, its selecting always articles without picture. the time is also long ... 0,6...0,7 sec –  May 10 '13 at 02:36
  • 1
    Looking at this (it is quite similar to my solution) I am not sure why the performance is poor. Possible that doing the subselect first and then STRAIGHT_JOINing that to the table might be faster. However the reason it isn't selecting a picture record is that it is selecting a random id which is less than the max id used by a picture record. This may or may not be a picture records (but with your data probably only about 1 time in 38000 will it be a picture record). Also if you have gaps in your record id sequence it will sometimes not bring anything back. – Kickstart May 10 '13 at 08:15