Complex mysql query, please help.

Question

I have a table of banners. Banners are marked with size id and slot id(position on page)

I need to pick 8 banners of one size, 1 for each slot between 11-18(slot id) and keep randomness each page view, so that each time banners were different if there any different for required slot.

The only solution i have found is this:

SELECT *
FROM banners
WHERE slot IS NOT NULL AND slot > 10 AND slot < 19
AND NOT IN (?)
GROUP BY slot
LIMIT 8

Keep the trace of pulled banners id and use them in next query.

But the problem is to calculate if there are 8 banners(maybe there is more banners for some slot id that in others). When to stop gathering ids for "NOT IN" and reset rotation.

Is there another solution to keep good random rotation of banners in this case?

Test table:

CREATE DATABASE IF NOT EXISTS `test123` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `test123`;


CREATE TABLE IF NOT EXISTS `test` (
  `banner_id` int(11) NOT NULL DEFAULT '0',
  `slot` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*!40000 ALTER TABLE `test` DISABLE KEYS */;
REPLACE INTO `test` (`banner_id`, `slot`) VALUES
 (284, 11),
 (283, 12),
 (282, 13),
 (280, 14),
 (281, 14),
 (278, 15),
 (279, 15),
 (277, 16),
 (276, 17),
 (274, 18),
 (275, 18);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Solution from Lieven:

(SELECT * FROM test WHERE slot = 11 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 12 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 13 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 14 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 15 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 16 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 17 ORDER BY RAND() LIMIT 1)
UNION ALL (SELECT * FROM test WHERE slot = 18 ORDER BY RAND() LIMIT 1)

What if i'll pick ids of all banners for slot 11 - 18 and set a flag in a session, so it wouldn't fetch all ids all the time. Then pick random ids via php and get banners for those ids, then remove those ids from session array and when array if empty, i'm repeating all steps?

This will produce 1 query per session and 1 query for picking banners itself.

Ofc this is not a solution for big tables with thousands of banner per 1 slot.

Or limit first query by 32 and then use all 32 ids in next query inside NOT IN(?)

Any better solutions? :)

Answer 1

You can make the order of returned results random by using MySQL's RAND() function.

How good the rotation would be depends on the implementation of randomization in MySQL.
I have no experience whatsoever with that.

edit

I think I finally understand the requirement. Unfortunately, I have no idea if following query works in MySQL.

          SELECT * FROM Banners WHERE Slot = 11 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 12 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 13 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 14 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 15 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 16 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 17 ORDER BY RAND() LIMIT 1
UNION ALL SELECT * FROM Banners WHERE Slot = 18 ORDER BY RAND() LIMIT 1

Answer 2

Without changing the structure I think Lieven's solution is the as good as it gets (taking into account performance and functionality).

However if you will decide to keep a counter and to store some more data, such as for example a log of last day or at least an ID of the last banner shown then functionally you could implement something much closer to exact rotation of randomly (or not) ordered banners.

Concept

For maximum flexibility I would add another column which specifies the oder - initialize to random value (or set it manually). Index on this column. Then I would keep last value shown for this column. Now you are able to select next one ordering on the column. If there are none wrap around.

Answer 3

Try this if you want. It is shorter :)

SELECT b.slot, (SELECT b2.banner_id FROM Banners b2 WHERE b2.slot = b.slot ORDER BY RAND() LIMIT 1) AS banner_id FROM Banners b WHERE b.slot > 10 AND b.slot < 19 ORDER BY b.slot ASC LIMIT 8

Answer 4

If you are not dealing with a large table you can do this. It will insure there are no banners picked in succession if you keep track of one number (via session) and always increment after fetching. Running an EXPLAIN will demonstrate how inefficient it is, but on a result set for 1000 rows it doesn't take too long:

SELECT a.* FROM (
    SELECT * FROM Banners WHERE Slot BETWEEN 1 AND 19 ORDER BY RAND(9)
) a
LEFT JOIN (
    SELECT * FROM (
        SELECT * FROM Banners WHERE Slot BETWEEN 1 AND 19 ORDER BY RAND(8)
    ) c GROUP BY c.Slot
) b ON b.PRIMARY_KEY_ID = a.PRIMARY_KEY_ID 
WHERE b.PRIMARY_KEY_ID IS NULL
GROUP BY a.Slot;

In this case you would replace RAND(9) with RAND(SESSION.INTEGER_VALUE+1) and replace RAND(8) with RAND(SESSION.INTEGER_VALUE). Dunno the language you are using but storing an integer in the session and incrementing it isn't a difficult task. Every time you run the query, increment the session integer value by 1.

There is still a possibility it will pull the same banner it already has in the chain, but it will always guarantee they won't see it right after each other.