Search code examples
mysqldatabaseindexingranking

Checking index key used by rank() function in sql

With EXPLAIN SELECT * FROM table1 where condition1 = condition1 and condition2 = condition2 you can check the index key being used if you have any.

If you have SELECT * RANK() OVER (ORDER BY condition3 DESC, condition4 ASC) AS table2 FROM table1 , can you index the condition3 and condition4 for the rank() function? Heck, how do you even check the index being used by the rank() function here if you have done indexing?

Solution

  • The answer is yes to both of your questions, and your rank query should benefit from the following index:

    CREATE INDEX idx ON table1 (condition3 DESC, condition4);

    Running EXPLAIN on the above query should show this index being used.