Python / Django: getting random articles from a huge table

Question

I have a huge table in my database (MySQL) with millions of data. I need to fill in 10 random data and show it in the user interface. What would be a good approach to considering performance?

I was thinking of creating a MySQL View to populate 10 random rows and read it from the user interface. Or is there another effective way to deal with this situation?

Answer 1

First of all (this is my own opinion), I am against using raw SQL when we are already working with a high-level structure like Django, if we do not find what we are looking for in the framework of (Django), so I would rather use Django for this :

Check this approach, it only works if you have set automatic PK increment for you and, of course, if the data is consistent (you do not delete the record from the table so you can be sure that all identifiers automatically increase)

import random # Getting the number of rows in the table it equivalent to do SELECT COUNT(*). count_record = Table.objects.count() # Choose 10 (at most) number from the list of all ids. random_pks = random.sample(range(1, count_record) , min(count_record, 10)) random_list = Table.objects.filter(pk__in=random_pks) 

if the condition indicated earlier is not fulfilled, I think you can do it with an raw SQL query, for example:

 query = """SELECT * FROM table ORDER BY RAND() LIMIT 10""") table.objects.raw(query) 

about performance, I think you need time, I hope this helps.

Answer 2

This can be expensive and slow, but:

 MyModel.objects.order_by('?')[:10] 

The main advantages are clarity and that it is not raw SQL.