Which are the differences and advantages on single INDEX or compound INDEX? I have two INDEX on a n:m relationship table as you can see on pics below:




Now I can have this also:



My questions:


What is the advantage from last way(pic compound) over the two first?
Can the # have some weight or significance in the composite INDEX?
Here's some explanation of which index you could use and when:

First, indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a record requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation. In the section "Loading Data Efficiently," we'll go into more detail about this phenomenon and what you can do about it.

Second, an index takes up disk space, and multiple indexes take up correspondingly more space. This might cause you to reach a table size limit more quickly than if there are no indexes.

Example:

mysql&gt; ALTER TABLE customer ADD INDEX idx_sub
 (subscription)
mysql&gt; EXPLAIN SELECT name FROM customer
WHERE subscription='2009-01-01' ORDER BY name
*************************** 1. row ***************************
 ...
 type: ref
 key: idx_sub
 rows: 4370
 Extra: Using where; Using filesort


The ideal way:

mysql&gt; ALTER TABLE customer ADD INDEX
 idx_sub_name (subscription,name)
mysql&gt; EXPLAIN SELECT name FROM customer
WHERE subscription='2009-01-01' ORDER BY name
*************************** 1. row ***************************
 ...
 type: ref
 key: idx_sub_name
 rows: 4363
 Extra: Using where; Using index


Benchmarks:


Avg number of sec to run the query:


Without index: 3.743
Index on subscription: 0.435
Covering index: 0.012

Covering index:


35x faster than index on subscription
300x faster than full table scan



Related: 


Better Indexing for MySQL?
Covering Indexes