Better Indexing for MySQL?

Question

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.

Answer 1

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!

Answer 2

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.

Answer 3

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.)

Answer 4

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.