Indexes and multi column primary keys

sql mysql primary-key indexing
David Jenings picture David Jenings · Jun 15, 2010 · Viewed 20.1k times · Source

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

Charles Bretana picture Charles Bretana · Jun 15, 2010

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), or (a,b,c) can also 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 column item alone, you need to add another index on that column by itself...

Related questions

SQL: set existing column as Primary Key in MySQL

I have a database with 3 columns: id, name, somethingelse This table has no index set and i am getting "No index defined!" in phpmyadmin id is a 7digit alphanumeric value, unique to each row. I want to set Drugid to …

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More
difference between primary key and unique key

I'm using mysql database. I have a confusion between primary key and unique key. Please help me where should I create primary and unique key. I mean in which situation we create unique key or primary key .

Read More