MySQL select order by

[Anon]

A slow query with "order by" in a select statement.

select from table limit 10; -- 0.01 second
select from table order by name limit 10; --20.01 second.

my dabase has 30000 rows only.

[[deleted]]

Do you have an index on the 'name' field?

[Anon]

yes, but my key define as follows:
KEY summary (name,sex)
PRIMARY KEY (id,name,sex)

I've try this:

select * from table order by name,sex limit 10; -- 20.01 seconds

select * from table order by summary limit 10; --- got the error: no such column,
but I use: mysqldump -d it's sure has key.

thanks,
jason koo

[[deleted]]

You can't order by 'summary', that's just a name for the index.

Slow queries can be caused by:
- a slow cpu
- a slow harddisk
- too little free memory
- a busy computer

[Anon]

has any solution for:
select * from table order by name desc limit 10;

I mean use another select sentence for the same purpose.

I've try this:
select * from table limit 30000,10; -- still need 20 seconds, seems MySQL under sequencial scan.

thanks,
jason koo

[[deleted]]

Try running the query with the 'explain' command:

explain select * from table order by....

[Anon]

yes, MySQL did full table scan when we use order by.
but got the same info of "explain..." --
select from table;
select from table order by...

how about change the parameter sort_buffer?
it's useful for order by?
thanks,
jason koo

[Anon]

I have exactly the same problem as Jason is having. Selecting rows out of over 300,000 entries is very quick yet when selecting them with the "ORDER BY" clause it takes over 500 times longer.

did you find a solution to this Jason?