MySQL Forums :: InnoDB :: simple and dumb question on index


Advanced Search

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


Subject Views Written By Posted
simple and dumb question on index 224 harry sheng 01/07/2017 12:53PM
Re: simple and dumb question on index 125 Peter Brawley 01/07/2017 02:19PM
Re: simple and dumb question on index 117 harry sheng 01/08/2017 04:11PM


Sorry, only registered users may post in this forum.

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.