Multiple ADD INDEX together or separately?

Question

I wanted to know the practical difference between this, in Mysql:

ALTER TABLE `tabela` ADD INDEX `col1` (`col1`);
ALTER TABLE `tabela` ADD INDEX `col2` (`col2`);

For this:

ALTER TABLE `tabela` ADD INDEX `col1col2` (`col1`, `col2`);

Since in both cases the INDEX is affecting both columns required.

If, for example, you use:

$mysqli->query("SELECT id FROM tabela WHERE col1 = 'qualquer' AND col2 > 0");

Would there be a difference in performance in using the separate INDEX method or together? If there are both (separated and together) would have some benefit in this case, or would have to manually define the query() to use INDEX "united"?

Answer 1

If you do multiple times the query you said:

$mysqli->query("SELECT id FROM tabela WHERE col1 = 'qualquer' AND col2 > 0");

Perhaps it would be more pertinent to do the composite index. Because it works exactly like the index of a book, imagine that if you have two information for you to find a chapter of a book for example, name and page number, soon you find that information more easily, the same principle governs the search of data in a database.

If queries based on each column separately are more frequent, it is more advantageous to create two indexes.

source: https://stackoverflow.com/questions/179085/multiple-indexes-vs-multi-column-indexes