select from table "user" query slow

[entertain]

I wanted to optimize some queries in iTrader and found a problem when loading the latest traders in combination with a huge user table.

A query looks like that:

		$itrader_shame_get = $vbulletin->db->query_read_slave("
			SELECT itrader_total, itrader_pcnt, username, userid, joindate
			FROM " . TABLE_PREFIX . "user
			WHERE itrader_total < 0
			ORDER BY itrader_total ASC
			LIMIT $mainpagelimit
		");

When you have a big user table, the query needs a few seconds to finish.

Do you have any idea how this could be enhanced?

[kh99]

I'm not a mysql expert by any means, but I think you could speed up that query by adding an index on itrader_total to the user table. What I don't know is what effect maintaining that additional index might have on other queries done to the user table (because of course changes to itrader_total need to modify the index).

[entertain]

That really helped to reduce the time of my page loads from 5 seconds to 0.1 second - thank you very much!

[Boofo]

From what I have been reading, indexes help "find the rows matching a WHERE clause quickly."