Multiple ADD INDEX together or separate?

Question

Multiple ADD INDEX together or separate?

Navigation

#1 by (2 votes)

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"?

php mysql

asked by anonymous 10.01.2016 / 20:30

1 answer

Page .html does not want to appear Question with formatted time return asp.net mvc

score 2 · Accepted Answer

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