I have two descending indexes on user_id and price fields:
<code>id_user:price_desc
id_user:price_asc
</code>
<code>id_user:price_desc
id_user:price_asc
</code>
id_user:price_desc
id_user:price_asc
No matter how I use the query:
<code>select * from t where user_id=1 order by price ASC
select * from t where user_id=1 order by price DESC
</code>
<code>select * from t where user_id=1 order by price ASC
select * from t where user_id=1 order by price DESC
</code>
select * from t where user_id=1 order by price ASC
select * from t where user_id=1 order by price DESC
the optimizer always chooses the descending index id_user:price_asc
in one case (order by price ASC) always
<code>Using index
</code>
<code>Using index
</code>
Using index
otherwise (order by price DESC)
<code>Backward index scan; Using index
</code>
<code>Backward index scan; Using index
</code>
Backward index scan; Using index
Why is that? Only forced index indication helps, but I don’t want to specify it in queries!
New contributor
Alex is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.