Indexes and multi column primary keys

Question

Went searching and didn't find the answer to this specific noob question. My apologies if I missed it.

In a MySQL database I have a table with the following primary key

PRIMARY KEY id (invoice, item)

In my application I will also frequently be selecting on "item" by itself and less frequently on only "invoice". I'm assuming I would benefit from indexes on these columns.

MySQL does not complain when I define the following:

INDEX (invoice), INDEX (item), PRIMARY KEY id (invoice, item)

But I don't see any evidence (using DESCRIBE -- the only way I know how to look) that separate indexes have been established for these two columns.

So the question is, are the columns that make up a primary key automatically indexed individually? Also, is there a better way than DESCRIBE to explore the structure of my table?

Answer 1

To return table index information, you can use:

SHOW INDEX FROM <table>;

See: http://dev.mysql.com/doc/refman/5.0/en/show-index.html

To view table information:

SHOW CREATE TABLE <table>;

See: http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

Primary keys are indexes, so there's no need to create additional indexes. You can find out more information about them under the CREATE TABLE syntax (there's too much to insert here):

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

Answer 2

I'm not that familiar with MySQL, but generally an multiple-column index is equally useful on the first column in the index as an index on that column alone. The multiple-column index becomes less useful for querying against a single column the further the column appears into the index.

This makes some sense if you think of the multi-column index as a hierarchy. The first column in the index is the root of the hierarchy, so searching it is just a matter of scanning that first level. However, in order to scan the second column, the database has to look up the tree for each unique value found in the first column. This can be costly enough that most optimizers won't bother to look deeply into a multi-column index, instead opting to full-table-scan.

For example, if you have a table as follows:

Col1 |Col2 |Col3
----------------
   A |   1 |   Z
   A |   2 |   Y
   A |   2 |   X
   B |   1 |   Z
   B |   2 |   X

Assuming you have an index on all three columns, in order, the tree will look something like this:

A
+-1
  +-Z
+-2
  +-X
  +-Y
B
+-1
  +-Z
+-2
  +-X

Looking for Col1='A' is easy: you only have to look at 2 ordered values. However, to resolve col3='X', you have to look at all of the values in the 4 bigger buckets, each of which is ordered individually.

Answer 3

I personally use phpMyAdmin to view and edit the structure of MySQL databases. It is a web application but it runs well enough on a local web server (I run an instance of apache on my machine for this and phpPgAdmin).

As for the composite key of (invoice, item), it acts like an index for (invoice, item) and for invoice. If you want to index by just item you have to add that index yourself. Your PK will be sorted by invoice and then by item where invoice is the same in multiple records. While the order in a composite PK does not matter for uniqueness enforcement, it does matter for access.

On your table I would use:

PRIMARY KEY id (invoice, item), INDEX (item)

Answer 4

I'm not intimately familiar with the internals of indices on mySql, but on the two database vendor products that I am familiar with (MsSQL, Oracle) indices are balanced-Tree structures, whose nodes are organized as a sequenced tuple of the columns the index is defined on (In the Sequence Defined)

So, unless mySql does it very differently, (probably not), any composite index (on more than one column) can be useable by any query that needs to filter or sort by a subset of the columns in the index, as long as the list of columns is compatible, i.e., if the columns, when sequenced the same as the sequenced list of columns in the complete index, is an ordered subset of the complete set of index columns, which starts at the beginning of the actual index sequence, with no gaps except at the end...

In other words, this means that if you have an index on (a,b,c,d) a query that filters on (a), (a,b), (a,b,c) can use the index, but a query that needs to filter on (b), or (c) or (b,c) will not be able to use the index...

So in your case, if you often need to filter or sort on item column alone, you need to add another index on that column by itself...