How to use index with Knex.js and MySQL

Question

I created an index for a column with Knex.js, but I can't find any way to use it.

knex('my_table')
.select('*')
.where('my_table.status', '2')

The index name is: my_table_stats_idx

I could do it with kex.raw() like:

FROM my_table USE INDEX(my_table_stats_idx)

But I had to use the a complex query building that didn’t fit with raw()

Is there a way to do that with Knex?

Answer 1

In SQL, you don't tell the database which index it should use. All you do is describe the result you want: the database makes the decision on how to process your statement. For this, the query planner takes in account various information, including, of course, available indexes. If it estimates that a plan that uses the index is optimal, the index is used - else it is not.

You don't tell what the definition of your index is. For your query, the optimal index probably is a single-column index on my_table(status).

Finally: in most databases, there are ways to suggest the database to use a given index, through hints. These are useful in edge cases where the query planner is not able to figure out things by itself. Your query clearly is not complex enough to qualify as such case.

Answer 2

You need knex.raw in the from clause

   knex
  .select('*')
   .where('my_table.status', '2')
  .from(knex.raw('my_table  FORCE INDEX(my_table_stats_idx)'))