1

Is it better to index each column individually with it's own key or to index multiple columns under the primary key?

I've seen it done both ways in many different applications and I don't see a clear difference.

  • 2
    As with all the best questions — it depends. You need unique constraints (indexes) on the combinations of columns for your candidate keys (one of which is your primary key; it may be the sole candidate key). You don't need a separate single column index on the leading column of the unique constraint indexes. Whether you need indexes on other columns depends on the queries you're going to be running. If they can be answered well with the candidate key indexes, the extras are just getting in the way. If there are frequently run queries which will benefit, then add extra indexes. Measure! – Jonathan Leffler Sep 9 '13 at 2:10
  • It depends....on your workload and whether you want to speed up particular queries – Mitch Wheat Sep 9 '13 at 2:10
2

First of all, your PRIMARY KEY depends on the logical nature of your data and will be indexed automatically by the DBMS. If the PK happens to be composite, it will automatically generate a composite index underneath it. The same goes for UNIQUE constraints and FOREIGN KEYs.

On top of that, under InnoDB, PK also acts as a clustered index.

As for the non-key fields, index based on the expected queries. Assuming your table T has several fields: A, B, C, D (etc.), for a query such as...

SELECT * FROM T WHERE A = '...' AND B = '...'

...create a composite index on {A, B} (or {B, A}), but not on C, D etc.

For the...

SELECT * FROM YOUR_TABLE WHERE A = '...' OR B = '...'

...you'll need a separate indexes on {A} and on {B}.

For both...

SELECT * FROM T WHERE A = '...' AND B = '...'
SELECT * FROM T WHERE A = '...' OR B = '...'

...you'll need indexes on {A, B} and {B} (or {B, A} and {A}).

For both...

SELECT * FROM T WHERE A = '...' AND B = '...'
SELECT * FROM T WHERE A = '...'

...you'll only need an index on {A, B}.

For...

SELECT * FROM T WHERE A = '...' ORDER BY B

...you'll need an index on {A, B}.

Etc, etc...

Do not create indexes that you don't need, since they incur a penalty in storage space and in INSERT/UPDATE/DELETE performance. For more on indexing (and other database performance considerations), I warmly recommend reading: Use The Index, Luke!

| improve this answer | |
  • +1. Useless indexes incur a performance penalty, because they provide no benefit but require resources (storage, memory, cpu.) The answer would be more complete if it considered the benefits of "covering indexes" for queries of the form SELECT a,c in addition to SELECT * queries. – spencer7593 Mar 27 '14 at 21:48
1

Don't index each column individually. Each table should have it's own primary key and possibly a foreign key to join tables and maintain references.

However, may be appropriate to index a individual column if you find yourself using that particular column in a lot of 'where' clauses. For example, I usually index a UPC column for example when dealing with product barcodes because I usually don't use the barcode as my primary key.

| improve this answer | |
  • 1
    this answer completely misses the point about covering indexes – Mitch Wheat Sep 9 '13 at 7:09
1

It really depends on which indexes are most suitable for the queries you run.

Creating a separate index on each individual column usually creates more indexes than are needed (and actually reduces performance). And when I run across that, I find that indexes that would actually improve performance are absent.

For the best performance, we usually want indexes that have leading columns that match the predicates in a query. For example, for a query like this:

SELECT t.b, t.d 
  FROM mytable t
 WHERE t.a = 123
   AND t.b < 50
   AND t.c = 'foo'

The most suitable index is likely ON mytable (a,c,b,d)

The equality predicates on a and c mean that we want to have those as leading columns, followed by b, so that a range scan can be done. By including the column d in the query, we can make it a "covering" index, so that the query can be satisfied from the index without a need for access (lookups) to the underlying data page. (The choice for the leading column, whether a or c should come first for this particular query really depends on cardinality (if there are only two possible values for a, half the rows with each of the values, and if the condition on a will match half the rows, but only 5% of the rows will satisfy the condition on c, then having c as the leading column would be preferred.)

For this example query, if we had only indexes on each individual column, MySQL will (usually) use only one of the index columns (MySQL 5.0+ can do an index_merge operation, using more than one index, but this rarely performs better than an access plan that makes use of a more suitable index.)

If MySQL uses only one index, say it chooses in the index on the a column, then MySQL can identify every row that has a value 123 in column a using the index, and that works fine. But then every one of those rows needs to be looked up from the pages in the underlying table, and the other columns have to be checked, to determine whether the row needs to be returned or not.

If the index were instead on columns a and c, then MySQL can "do the check" on both of those columns, and have fewer rows to lookup from the pages in the table.

Also note that if you include an ORDER BY clause in the query, then MySQL may be able to make use of an index to return the rows in the specified order using an index, and avoiding a "Using filesort" operation. To get that to happen, the leading columns in the index need to match the columns in the ORDER BY clause.


In summary, usually, the addition of an index on each individual column is done without regard to the actual queries that are going to be run. On large tables, it's better than not having any index, and performance of some queries will be improved. But you also pay a price in performance for INSERT, UPDATE and DELETE operations, when those indexes need to be maintained, which is a lot of extra work (cpu, memory and disk) to maintain indexes that aren't useful.

For optimum performance, a much better approach is to actually examine the queries that need to run, and create indexes which are ideally suited to those queries.

(Obviously, you need an index on the primary key. You need an index on every set of columns you want to enforce a unique constraint on. And you want each foreign key column to be the leading column in an index.)

| improve this answer | |
  • Thanks spencer. So what would be a good baseline to determine the most important columns to index? Obviously all the columns are needed at one point or another or they wouldn't exist. I'm working with OpenCart on existing tables and models. Another developer I know sent me over a list of "tweaks" (adding foreign key indexes) and I'm trying to get a better understanding of why they will help, if I should add them as separate keys, or add them to the primary key as covering indexes. – Vince Kronlein Sep 9 '13 at 3:03
  • The EXPLAIN output is the probably most important tool. Each foreign key column needs a separate index (KEY), so that the column can be the first column in the index. This is important for performance of queries that do JOIN on the foreign key column, and referential integrity checks for DML on non-MyISAM tables; with MyISAM the app is likely doing queries for these checks, so the indexes will help performance of those queries. – spencer7593 Sep 9 '13 at 5:04
0

Take a look at High Performance MySQL: Optimization, Backups, Replication, and More. This book will answer all your questions. In short, as many other guys said, it all depends on queries that you plan to run. If you posted them here, it would be easier to give you an advice.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.