indexing - MySQL query with less than and ORDER BY DESC -

i'm doing work rather large set of data , trying create query every combination of 4 different pieces of data. of pieces combined form staggering 122,000,000 rows. then, i'm trying find weight less amount , sort value highest lowest.

i can use weight < x no problem.

i can use weight < x order height asc no problem.

i can use weight < x order height desc when x around both upper , lower end. once starts creeping middle, rises seconds, minutes, "i'm not going wait long."

any thoughts? (the names have been changed, types have not)

the create:

create table combinations (     id bigint(20) unsigned not null auto_increment,     smallint(2) not null,     left smallint(2) not null,     right smallint(2) not null,     down smallint(2) not null,     weight decimal(5,1) not null,     width smallint(3) not null,     forward decimal(6,2) not null,     backwards decimal(5,2) not null,     in decimal(7,2) not null,     out smallint(3) not null,     height smallint(3) not null,     diameter decimal(7,2) not null,     primary key  (id) ); 

the index

alter table combinations add index weight_and_height(weight,height); 

the query

select * combinations weight < 20 order height desc limit 0,5; 

the explain

| id | select type | table        | type  | possible_keys     | key               | key_len | ref  | rows |       | |  1 | simple      | combinations | index | weight_and_height | weight_and_height | 5       | null |   10 | using | 

your index used filtering on weight. here steps:

1. all rows weight < x (where) found (using index starting weight)
2. that set sorted (order height ...)
3. 0 (offset) rows skipped;
4. 5 (limit) rows delivered.

the potentially costly part step 1. in example "20" in list. in fact explain estimated set had 10 rows. bigger values of x, step 1 takes longer. unavoidable.

all rows step 1 processed; hence, time step 2 varies. (5.6 has optimization partially combines steps 2,3,4.)

are doing select *? if, example, wanted select id, index(weight, height, id) run lot faster because query can performed in index.

if did need query mentioned, run faster:

select c.*     (         select id combinations             weight < 20 order height desc limit 0,5          ) ids     join combinations c  using(id)     order height desc; 

notes:

• the subquery "using index" mentioned.
• only 5 rows delivered subquery.
• the outer select has mere 5 rows deal with.
• id indexed (because primary key), join efficient.
• (re: title) "less than" , "desc" not significant.