Multiple ADD INDEX together or separate?

1

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 INDEX is affecting both of the required columns.

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 you have any benefit in this case, or would you have to manually set query() to use INDEX "joined"?

    
asked by anonymous 10.01.2016 / 20:30

1 answer

2

If you do multiple times the query that said:

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

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

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

source: link

    
11.01.2016 / 12:06