Implication of multiple MySQL keys with the same name-CodePudding

Using separate queries I can add non-unique indexes to a MySQL table, each with its own key name, like this:

But when I use the database forge class of my PHP framework (CodeIgniter 3) to add several keys during a CREATE TABLE query, they all get the same Key_name, but with a different Seq_in_index value.

PHP Code:

$this->dbforge->add_key(['estimate_id', 'order_number']);
// produces: KEY `estimate_id_order_number` (`estimate_id `, `order_number `)

Resulting keys:

I don’t really know which is "better" so to speak. The latter table configuration seems to indicate that a single key is used to index both estimate_id and order_id.

Will that cause any problems, such as ambiguity between the fields?

Are there any performance implications for either table design?

What sort of situations might require distinct key names?

CodePudding user response：

This is a composite key also known as a composite index. Multiple columns are included in the BTREE index.

In your case the key is defined as

 ALTER TABLE tbl
     ADD KEY estimate_id_order_number (estimate_id, order_number)

The key's name estimate_id_order_number is just a name. You can use that name to drop the key later if you decide you don't need it. You can use it to remind you the purpose of the index. It shows up in query plans. And if you must use an index hint, you use the index name. It serves no other purpose -- none -- during queries.

If you have a query like

SELECT id FROM tbl WHERE estimate_id = 123 AND order_number = 321

SELECT id FROM tbl WHERE estimate_id = 123 ORDER BY order_number

this index will make it run very fast: the query can be satisfied from the composite key.

But if you have

SELECT id FROM tbl WHERE order_number = 321

it won't help: the composite index only helps when there's a WHERE clause mentioning its first column. But a key with the columns (order_number, estimate_id) will help.