Assume I have a very large table X and I want to run below SQL.

select * from table X where type='X1234' order by time;

type is not unique and indexed ( with high cardinality), and there are very few rows in one type ( assume 1 - 5 ). Data size of each row is about 50 byte to 50K byte

Do I still need index for time column?

I think although the table is very large, but "where" clause should be executed before "order by", that will result in a small dataset and no index is needed for column time. Am I right?



I have tested with composite index ( type, time ), and single index ( type ), the report explain shows as below.

Composite index 
| id |type |ref   | rows | filtered | Extra                 |
|  1 |ref  |const |    3 |   100.00 | Using index condition |

Single index
| id |type |ref   | rows | filtered | Extra                                 |
|  1 |ref  |const |    3 |   100.00 | Using index condition; Using filesort |

There is no noticeable difference in execution time. It is likely the dataset is small and the filesort operation was entirely performed in buffer cache, so it is still very fast.

In case the dataset become larger, then using composite index should be a good idea.

  • 2
    If resultset contains 1..5 records, no altering indices needed. Moreover, additional index by time will not affect at all - only one index can be used in query type You show. Ever resultset is large (but in this case composite index by (type,time) will improve). The main direction in optimization is to replace asterisk with the list of columns You really need - maybe it's possible to create covering index. – Akina May 30 '18 at 16:04
  • 1
    Why don't you run an EXPLAIN PLAN or whatever the syntax is for your RDBMS (which you didn't tag - could you do this?) for both scenarios! – Vérace May 30 '18 at 17:19
  • @Vérace thanks for the suggestion . I'm new to MySQL and didn't know it has EXPLAIN feature. I will try it later. – user8100646 May 31 '18 at 1:08
  • @Akina thanks for the suggestion and direction. covering index and composite index is new to me and I just have learned that. For my case, I am going to extract all columns from a table, so covering index may be not effective in this scenario. I will try it with composite index later. – user8100646 May 31 '18 at 1:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.