How to correctly index the following?

Question

I have the following table (file_category_tbl) to represent connections between files and categories.

fileId - bigint(20)         
categoryId - bigint(20)
order - int(10)

So that the files in a category can be ordered, I have an order field... Therefore my question is what indexes would I require for optimum performance on the following:

SELECT * FROM file_category_tbl WHERE categoryId="3" ORDER BY order ASC

I have a unique index a UNIQUE (fileId ,categoryId); As there can't be the same fileId with the same categoryId. I have also got an index on categoryId, as this is what is being searched on. I have also got an index on order?... but is this neccessary? as it is only doing an orderBy on this...

Kind Regards to any responder... J

Answer 1

As documented under ORDER BY Optimization:

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

[ deletia ]

• The key used to fetch the rows is not the same as the one used in the ORDER BY:

  SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

Therefore your current indexing cannot be used for performing the sort operation. However, the same page also documents:

The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part:

[ deletia ]

SELECT * FROM t1
  WHERE key_part1=constant
  ORDER BY key_part2;

Therefore a compound index over (categoryId,order) can be used for both the filter and the sort operations, which is the optimal outcome for this query.

Answer 2

According to my understanding, your indexing is sensible and will help towards performance of your query. But I would also suggest you to have aPrimary Key index in your table rather than only having the combined Unique Index

The reason I'm telling this is, If you want to refer to any of your records of this table, may be to delete it or do any other function, a primary key would be useful. On the other hand it might actually downgrade the performance of your query because you are requiring all the fields and now with the primary key field, you have to bring in 4 fields. As a solution for that, you can specify what columns you want in your result.

Hope this makes sense :-)