[Solved] sql order by rand() with highest value shown more frequently

EverSQL Database Performance Knowledge Base

sql order by rand() with highest value shown more frequently

Database type:

As title describes, im looking to write some SQL (mySQL DB) thats displays random records with highest value shown more frequently then others.

select CPC from advertisement ORDER BY RAND(), CPC DESC

The above code does not work as expected.

heres what im after:

I have 10 advertisers each setting their own cpc (cost per click) budget. The advertiser with the heighest budget will have his/hers advert show more frequently then the others, yet all being random.

user_id | cpc  |
------------------
1       | 0.10 | 
2       | 0.03 |
3       | 0.20 |
4       | 0.04 |
5       | 0.55 |
6       | 0.12 |

So user 5 will have his advert displayed more freqently then the others 5, 3, 6, 1, 4, 2 - in order of most impressions respectively.

Bit like I assume google adwords work, the higher the users budget the more impressions he/she will have.

I know no one likes RAND() due to performance but I will have no more then 100 advertisers.

Regards

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Mixed Order By Directions Prevents Index Use (query line: 7): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
Optimal indexes for this query:
ALTER TABLE `advertisement` ADD INDEX `advertisement_idx_cpc` (`CPC`);
The optimized query:
SELECT
        advertisement.CPC 
    FROM
        advertisement 
    ORDER BY
        RAND(),
        advertisement.CPC DESC

Related Articles



* original question posted on StackOverflow here.