Indexes and multi column primary keys

Question

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?

Thanks, everyone. As usual, great information from a really generous community. Answers seem to focus on sorting vs selecting. Are the indexes just as important for selecting? I'll be using select statements that will return between 1 and ~10 rows. In my situation, the order really won't matter. — David Jenings, Jun 15 '10 at 23:06
indices are more important for selecting when the query returns few rows, and more important for sorting when queries return many rows... sorting 10 rows is trivial. finding them is hard. If the query returns the whole table, (or a substantial chunk of it) then finding the rows is not an issue, but sorting that large resultset is... — Charles Bretana, Jun 23 '10 at 16:30

Charles Bretana · Accepted Answer · 2017-01-23T13:03:22.627

80

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

edited Jan 23 '17 at 13:03

answered Jun 15 '10 at 19:32

Charles Bretana

143,358
22
150
216

3

This is exactly how Mysql does it. Here is a ref : http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html – Nikhil Sahu Jan 05 '17 at 18:35
@Nikhil, Thanks for the reference! – Charles Bretana Jan 05 '17 at 18:55
Very simple and clean explanation! It also fits to MySQL implementation. Thanks. – csonuryilmaz Jan 23 '17 at 08:52
I do not agree with this answer. The explanation is correct for composite indices, but not for multi-column primary key. – cppcoder Apr 04 '17 at 09:10
This does not answer the users question in if using an index on a single column that is also part of the multi primary keys is necessary. – Ron Ross Jul 31 '18 at 04:27
1

Yes it does. Read the last sentence. The text prior to that lays the groundwork for understanding the answer. Otherwise, I could have just said "No". – Charles Bretana Jul 31 '18 at 13:24
@CharlesBretana What about inner join ? Which column (a or b or c or d) will be suitable for inner join condition ? – gaurav kumar Jan 16 '19 at 03:32
Joins have nothing to do with it. The presence of, or the usability of an index to support the processing to implement a join is a separate question. You can join on anything, regardless of what indices exist. If an index exists that supports the join processing logic the query optimizer will use it, if not, it will still join, it will just perform a brute force (table-scan) comparison. But whether an index can be used for a join conforms to the same rules as above.... {a}, or {a,b}, or {a,b,c}, or the complete set of join columns, if specified in the join, would allow the index to be used – Charles Bretana Apr 04 '19 at 15:08

score 19 · Answer 2 · answered Jun 15 '10 at 19:19

19

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)

answered Jun 15 '10 at 19:19

George Steel

697
5
7

thank you for the information and for clear and easy to understand answer, you saved me a lot of time :) – Accountant م May 21 '16 at 01:32
2

This should be selected as answer as it explains that multi primary key is used like index and uses left prefix, so creating an index with item column is useful and not redundant, while the creating an index for the invoice column would be redundant due to left prefix access. – Ron Ross Jul 31 '18 at 04:30

score 5 · Answer 3 · answered Jun 15 '10 at 19:12

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.

score 4 · Answer 4 · answered Jun 15 '10 at 19:04

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

score 4 · Answer 5 · answered Apr 04 '17 at 09:17

There is a difference between composite index and composite primary key. If you have defined a composite index like below

INDEX idx(invoice,item)

the index wont work if you query based on item and you need to add a separate index

INDEX itemidx(item)

But, if you have defined a composite primary key like below

PRIMARY KEY(invoice, item)

the index would work if you query based on item and no separate index is required.

Working example:

mysql>create table test ( col1 int(20), col2 int(20) ) primary key(col1,col2);
mysql>explain select * from test where col2 = 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 8       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

This answer seems to contradict with the selected answer and https://dba.stackexchange.com/questions/14256/how-is-index-on-composite-primary-key-in-mysql — Rich, Aug 10 '22 at 19:01

score 0 · Answer 6 · answered Feb 11 '19 at 02:51

Mysql auto create an index for composite keys. Depending on your queries, you may have to create separate index for individual column in the composite key.

If you are using mysql workbench, you can manually right click the schema and click on edit to see everything about the table

score 0 · Answer 7 · answered Aug 05 '22 at 08:30

If your query is using both columns in where clause then you don't need to create a separate index in a composite primary key.

EXPLAIN SELECT * FROM `table` WHERE invoice = 1 and item = 1

You are also fine if you want to query with first column only

EXPLAIN SELECT * FROM `table` WHERE invoice = 1

But if you want to query with subsequent columns col2, col3 in composite PK then you would need to create separate indexes on those columns. The following explain query shows the second column does not have a possible key detected by MySQL

EXPLAIN SELECT * FROM `table` WHERE item = 1

Indexes and multi column primary keys

7 Answers7

Linked

Related