Hi,
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