Page 1 of 1

MySQL and ORDER BY to speed up query

Posted: Mon Oct 17, 2011 9:27 pm
by sn4k3
Hi,

my question is simple, can ORDER BY improve a query speed and performance when we are searching for a number using an indexed field?
Or it doesn't matter for mysql?

i explain better:
My Table wrote:CREATE TABLE `serverplayer` (
`id` int(10) unsigned NOT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`serverid` int(10) unsigned NOT NULL,
`jointime` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `serverid` (`serverid`),
CONSTRAINT `serverplayer_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `serverplayer_ibfk_2` FOREIGN KEY (`serverid`) REFERENCES `server` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
so looking at this query:

Code: Select all

SELECT * FROM serverplayer WHERE serverid = 525 ORDER BY serverid LIMIT 20

Code: Select all

SELECT * FROM serverplayer WHERE serverid = 525 LIMIT 20
So witch one will perform best and faster?
Since ORDER BY put all serverid in order it will be more fast for mysql search and select?

Thanks

Re: MySQL and ORDER BY to speed up query

Posted: Mon Oct 17, 2011 9:45 pm
by Weirdan
I don't think additional ordering can speed up the query (unless you're using group by + order by null).

Re: MySQL and ORDER BY to speed up query

Posted: Mon Oct 17, 2011 9:52 pm
by sn4k3
Hum, i'm just asking because it make any logic, but i dont know how engines works

think if you have 10 000 rows you are searching for all serverid (525)
Now on table you have some in the begin, others in middle, others in end

so mysql must go and search from begin to end
is not faster by using a ORDER to get things together?

Thanks

Re: MySQL and ORDER BY to speed up query

Posted: Mon Oct 17, 2011 11:14 pm
by Benjamin
The answer is NO. MySQL only uses 1 index per table.

Re: MySQL and ORDER BY to speed up query

Posted: Tue Oct 18, 2011 9:49 am
by sn4k3
ok thanks, its good to know this little things :)

Re: MySQL and ORDER BY to speed up query

Posted: Wed Oct 19, 2011 2:32 am
by VladSun
sn4k3 wrote:so looking at this query:

Code: Select all

SELECT * FROM serverplayer WHERE serverid = 525 ORDER BY serverid LIMIT 20

Code: Select all

SELECT * FROM serverplayer WHERE serverid = 525 LIMIT 20
So witch one will perform best and faster?
Since ORDER BY put all serverid in order it will be mor e fast for mysql search and select?
Using ORDER BY on a result with all values of the serverid column set to 525 just doesn't make sense. Just create an INDEX for serverid column.