MySQL Performance: MySQL => Optimize ORDER BY

Members

Help

Home

Home » Performance » MySQL » Optimize ORDER BY

Show: Today's Messages :: Show Polls :: Message Navigator

Optimize ORDER BY [message #3189]

Tue, 17 June 2008 11:04

makama
Messages: 1
Registered: June 2008

Junior Member

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

Report message to a moderator