Tom Smith wrote: > Hello. > > When in mysql I do a... > > explain select title from burningahole_product where ( price >10 and > price<20) order by random_number limit 10; > > > ... I get... > > +----+-------------+----------------------+------- > +----------------------------+----------------------------+--------- > +------+--------+-----------------------------+ > | id | select_type | table | type | > possible_keys | key | key_len | > ref | rows | Extra | > +----+-------------+----------------------+------- > +----------------------------+----------------------------+--------- > +------+--------+-----------------------------+ > | 1 | SIMPLE | burningahole_product | range | > burningahole_product_price | burningahole_product_price | 12 | > NULL | 209932 | Using where; Using filesort | > +----+-------------+----------------------+------- > +----------------------------+----------------------------+--------- > +------+--------+-----------------------------+ > > ...which is VERY slow at times... > > Is it possible to do a ... > > force index (burningahole_product_random_number) > > ...in Django? Which seems to make the "Extra" = "Using where" rather > than "Using where filesort". > > Or does anyone else know how I can speed up the above query on a > table with close to a million records. I have tried adding indexes > all over the place, but in general mysql seems to look at ALL the > records (or close to) before doing a query. > > > > Thanks > > tom > Mysql is telling you right there how many records it IS looking at. It's using a range index on your price column and reducing the number of rows it scans to 209,932 (from the million or whatever the total count is), no other indexes on that table will make a difference for this query. The filesort is happening because of your Order clause.
The second link posted in a previous message shows the ORDER BY RAND() LIMIT 10 syntax, which works, but in a large table it's still going to take some time to process (still shows filesort too). In the comments of that page another person mentions to use something like WHERE RAND() > 0.9 ORDER BY RAND() LIMIT 10, but I didn't see much added benefit when running it on my own large tables (same Explain result too as the previous one). Give those a shot though and see if they help anything. You may have to take this into the application to speed it up, but with 200,000 records to pick 10 random from, that may be expensive as well. Is there no other reduction in records you can do? Like a category, or date added? Jay --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
