#7760: Queries on large primary tables with limit/offset clauses are slow ---------------------------------------+------------------------------------ Reporter: henrybaxter | Owner: nobody Status: new | Milestone: Component: Uncategorized | Version: SVN Keywords: database,admin,slow,query | Stage: Unreviewed Has_patch: 1 | ---------------------------------------+------------------------------------ When django's db layer needs to select_related() on a large initial table with a number of joins and it has a limit or offset clause, it generates slow queries.
SELECT * FROM big_table INNER JOIN t1 ON big_table.t1_id ... INNER JOIN tn ON big_table.tn_id LIMIT 10 OFFSET 20 This query will join the whole table (even if it has a 1 million records) and THEN do the limit. This is WAY faster: SELECT * FROM (SELECT * FROM big_table LIMIT 10 OFFSET 20) INNER JOINs... This type of query change does not work when there is a WHERE clause that references a product of the JOIN though. If the WHERE clause only references the primary table, then it still works great. I have submitted a patch to help demonstrate the problem - it only works when there are no WHERE clauses at all (which is almost pointless), but it works for my particular purpose (pagination in the admin interface with no filters or searching) I would love to hear from another developer how to integrate this functionality (or if it can actually be done without changes to the django codebase) -- Ticket URL: <http://code.djangoproject.com/ticket/7760> Django Code <http://code.djangoproject.com/> The web framework for perfectionists with deadlines --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django updates" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-updates?hl=en -~----------~----~----~----~------~----~------~--~---
