MySQL and ORDER BY to speed up query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

MySQL and ORDER BY to speed up query

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: MySQL and ORDER BY to speed up query

Post by Weirdan »

I don't think additional ordering can speed up the query (unless you're using group by + order by null).
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: MySQL and ORDER BY to speed up query

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: MySQL and ORDER BY to speed up query

Post by Benjamin »

The answer is NO. MySQL only uses 1 index per table.
sn4k3
Forum Commoner
Posts: 37
Joined: Tue Oct 16, 2007 3:51 pm

Re: MySQL and ORDER BY to speed up query

Post by sn4k3 »

ok thanks, its good to know this little things :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL and ORDER BY to speed up query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply