mysql order by query, trying to randomize results by pairs of char_length

Question

Hi all,

I'm working on displaying a page of classifieds in table rows. The problem I'm trying to solve is to arrange the ads in such a way that reduces the amount of white-space on the page, while maintaining a random order.

A diagram of what a couple of unordered ads look like:

_______________ __________________
ad text here.  |  another ad here
this ad has    |  (2)              
more text than | [ unwanted 
that ad. (1)   |   white-space ]
_______________|__________________

What I'd like to do is order the results by char_length, but also randomize the results in groups of 2, or 3, or whatever.

The query I have now is: SELECT * FROM ads ORDER BY CHAR_LENGTH(adtext) limit $page, $ads_per_page (using PHP)

That gives me results that look like:

_______________ ___________________
short ad. (1)  | another short ad. (2)
_______________|___________________
ad that's a (3)| another little
little longer. | longer ad. (4)
_______________|___________________
ads keep (5)   | this ad has the 
getting longer | most text out of
in char_length | all the ads... (6)

This is great for reducing the white-space, but I still need those results randomized. Is it possible in MySQL to then randomize those results in groups of 2, or 3, or whatever?

In other words, is there something that would give me results like the following:

    _______________ ___________________
    ads keep  (1)  | this ad has the 
    getting longer | most text out of
    in char_length | all the ads... (2)
    _______________|___________________
    short ad. (3)  | another short ad. (4)
    _______________|___________________
    ad that's a (5)| another little
    little longer. | longer ad. (6)

Any ideas?

Thanks, Eli

Answer 1

This is a non-answer, but

I'd suggest moving this logic up to the client. You'll have a much easier time of it with higher-level languages than within your database.

Answer 2

if it doesn't have to be random, just look randomish, you could build the rows ordered by char count first then order rows by substring(3,1) or something.

also I agree with p.campbell this would be much easier to do not within the database.

Answer 3

what about this:

set @N = 0;
SELECT * FROM (
SELECT @N := @N +1 AS number,id,text FROM ads ORDER BY CHAR_LENGTH(text) limit 100 
) as ads ORDER BY (case when ads.number % 2 = 0 then 1 else 0 end) , RAND() 

first select everything ordered by length of the field, add a counter. then in a second query devide which text goes into which column by jut looping through one after the other, thats the first sort criteria (modulo 2 is for your two columsn, you could as well do more) and then randomize the results in each column.

please be aware that the mysql rand function is very, very slow. you should use some optimized version andy ou have to add your limit conditions