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.
Are the columns that make up a primary key automatically indexed individually? Is there a better way than DESCRIBE to explore the structure of my table?
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 (B-Tree) structures, whose nodes are organized as a sequenced tuple of the columns the index is defined on (In the Sequence Defined) See Note
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), or (a,b,c) can also use the index, but a query that needs to filter on (b), or (c), (b,c) or (a, c) will not be able to use the index...
So in your case, if you often need to filter or sort on column item alone, you need to add another index on that column by itself...
NOTE: Normally, when using the term Balanced tree, it is implied that each node in the tree may have a maximum of two sub-nodes or branches. This is then called a Balanced Binary tree. Indices in SQL Server are just balanced non-binary B-Trees, where the nodes can have more than two sub-nodes or branches. The index optimizer calculates how many branches to allocate to each node based on optimizing performance. The fewer sub-nodes that exist on each node, the taller (height or depth) the tree will be. The optimum number of branches (that maximizes performance in locating a specific record), depends on the number of discreet values of the index attributes within the total data set domain.