MySQL Forums
Forum List  »  InnoDB

simple and dumb question on index
Posted by: harry sheng
Date: January 07, 2017 12:53PM

Say I have a fact table with 3 columns as primary key, and 1 column for value.
One of the primary key column is datetime type, all other columns are BIGINT type.

One of the primary keys has 2k unique values, another has 16 distinct values, and every datetime value repeats 16k to 32k times in the table.

The total number of records may grow up to more than 100M.

I need to support very selective query and not-so-selective (say return 1/100th to 1/10th of the whole table) query.

Let's put the disk requirement and update time performance aside, will there be any performance benefit for query if I add an index which include all the columns, the key coloumns and the value column? I mean, I get everything from the index, I do not need to go to the table, right? And the index size will be much smaller than the table, that's the argument point here.

I know this is an odd case and an odd question. Just curious.

Thank you very much.

Options: ReplyQuote

Written By
simple and dumb question on index
January 07, 2017 12:53PM
January 08, 2017 04:11PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.