Optimize ORDER BY

  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize ORDER BY


    I hope someone can help me with the following problem.

    (Note: I will simplify my table structure to the essential)

    I have two tables, one containing objects and one containing the objects a user has, so basically I have

    table_userObjects with PRIMARY KEY idUserObject INDEX ON idObject (not unique)
    table_objects with PRIMARY KEY idObject INDEX ON name

    What I want to optimize is the query which gets the objects for one user and sorts them by name. For example:

    SELECT *
    FROM table_userObjects,table_objects
    WHERE table_userObjects.idObject = table_objects.idObject
    AND table_userObjects.idUser = 3
    ORDER BY table_objects.name

    The db has around 40000 different objects and the top users have 200000 different items. In this cases it takes around 6 seconds to run the query.

    Is there anyway to create an index on table_userObjects, based on the name of the objects from table_objects? Or some other way to speed up this query?

    Please help, I'm getting desperate, my best choice until now is to simply add a field 'name' to the table_userObjects and redundantly store the name for each object, so I can index on that. But there must be a better way!

    Thanks in advance

  • #2
    Try indexing the name -- you're ordering by that. Index that, and you should see your speed increase.