0

My table have 10 columns but i will use 5 columns more frequent. which is the better way to create an index like do we have to create index in all the 5 columns or composite index do we have to create. Actually performance is main thing to consider i don't have much idea in tuning side.

Explorerer
  • 1
  • 3
  • 1
    Basically you should create indexes on column which are more used in your WHERE, ORDER BY, and GROUP BY clauses, not based on your select columns. – PSK Feb 20 '19 at 07:07
  • I removed all the conflicting DBMS tags. Please add **only** the tag for the DBMS product you are really using? Indexing strategies will be different for different database products. –  Feb 20 '19 at 07:15
  • Okay thanks. a_horse_with_no_name – Explorerer Feb 20 '19 at 07:21
  • @PSK Yes i'm using those 5 columns in my where clause / some join conditions – Explorerer Feb 20 '19 at 07:24
  • @Explorerer This depends on how you reference your table in your queries exactly. It's impossible to say which indexes to create for such a vague description like `using those 5 columns in my where clause / some join conditions`. Some RDBMS have so called `index advisers` which can recommend you a set of indexes for some particular query and even for a set of queries. – Mark Barinstein Feb 20 '19 at 09:42
  • Show us the queries; we can help you optimize the indexes. Or use my Cookbook: http://mysql.rjweb.org/doc.php/index_cookbook_mysql Throwing all 5 into a single composite index is _not_ likely to help much. – Rick James Jan 13 '20 at 01:54

0 Answers0