0

I have one table with 33,107,887 records.

table structure: tablename 

id - bigint - uniqu
text - text - fulltex index
cate_id - bigint - btree index
marchant_name - varchare - btree index
created_on - timestamp - btree index

When i run below query then its takeing 60+ seconds 

SELECT * FROM tablename where marchant_name='marchant name x' order by id  desc limit 20

but if i run this query then its take just 1 second

SELECT * FROM tablename where marchant_name='marchant name x' order by id  asc limit 20

Note : Here marchant name x has 8mn+ records in main table.

Hear just issue with "oder by asc" and "order by desc" . Is there anyone who can help me?

Thanks

ffflabs
  • 17,166
  • 5
  • 51
  • 77
Hitest mail
  • 1
  • 1
  • Can you add the results of `EXPLAIN` for both queries? Also, is there index for `(merchant_name, id)` created? – raina77ow Nov 09 '17 at 14:48
  • both query are same just their order by different. like, order by id desc limit 20 and order by id asc limit 20 – Hitest mail Nov 09 '17 at 14:51
  • It's quite clear from the original post. What I'm asking for is that you add the results of running `EXPLAIN` on both queries to it - as it might be hinting on why's the difference that drastic. Also note that two separate indexes on two columns are not the same as composite index for two columns. – raina77ow Nov 09 '17 at 14:55
  • Possible duplicate of [MySQL ORDER BY DESC is fast but ASC is very slow](https://stackoverflow.com/questions/2886575/mysql-order-by-desc-is-fast-but-asc-is-very-slow) – AddcitedToLearn Nov 09 '17 at 15:19

0 Answers0