Mysql order by query trying to randomize results with char_length pairs

Question

I am working on displaying an ad page in the rows of a table. The problem I'm trying to solve is to arrange ads in such a way as to reduce the number of spaces on the page while maintaining an arbitrary order.

A diagram of what several disordered ads look like:

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

What I would like to do is organize the results using char_length, but also randomize the results in groups of 2 or 3 or whatever.

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

This gives me results that look like this:

_______________ ___________________
short ad. (1)  | another short ad. (2)
_______________|___________________
ad that 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)

, . MySQL 2 3 - ?

, -, :

    _______________ ___________________
    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 a (5)| another little
    little longer. | longer ad. (6)

?

, Eli

Answer 1

,

. , .

Answer 2

, , , char count , (3,1) -.

p.campbell, .

Answer 3

:

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 field length, add a counter. then in the second query print the text into which the column moves one after the other, that is, the first sorting criterion (modulo 2 for your two columsn, you can also do more), and then randomize the results in each column.

Remember that the mysql rand function is very, very slow. you must use some optimized version and you must add your limit conditions.