An easy way to think about indexes is to think of a summary of a book, it exists to facilitate the location of contents of the book, so you find content easier than looking for page by page, for this depends on which the indexes are well defined.Benefits:They are able to reduce the amount of data the server would have to examine.They are able to help the server sort (e.g. ORDER BY) and avoid creating temporary tables.The use of "are capable" is because not all created indexes will do this, even because maybe they are never used.Types of Indexes:These are the types of indexes that exist in engines most common:B-Tree: Most common of all types of indexes, basically with the exception of https://dev.mysql.com/doc/refman/5.7/en/archive-storage-engine.html all bear it, but with internal differences.
InnoDB Engineer uses uncompressed B+Tree and are referenced by Primary Key._ Engineer uses B-Tree with understanding techniques and are referenced by their physical location.NDB Cluster Engine uses T-Tree.TokuDB uses a call variation Fractal Tree.Hash: His own name already explains, he is only utilised for differentiation and equality (IN(), = and <=>), but is not used for any other functionality.
MEMORY Engineer is one of the only ones that has explicit support for this type of index.InnoDB Engineer possesses adapttive hash indexes, when the InnoDB identifies that some values are often accessed it creates a hash index and uses before the B-Tree, but this is fully automatic and there is no way to shut down or create this.R-Tree: Rarely used in MySQL, but used in PostgreSQL and PostGIS, it does not require the use of WHERE to benefit.
InnoDB Engineer is one of the only ones that has support for this type of index.Full-Text Index: Have a different feature and only used for operations MATCH AGAINST.
_ Engineer has support for this type of index.InnoDB Engineer has support for this type of index in the latest versions.To complement, ScaleDB uses Patricia-Tries. Already the InfiniDB and Infobright have its own architecture (?).As the pattern is the B-Tree, it is precisely in it that I will use as basis for everything here.Limitations:They are not for readings that do not start from the left, for example SELECT * FROM Tabela WHERE Nome LIKE '%A', to try to find all names ending with Abut you can use A% to find what starts with A.Your query must execute the same order the defined indexes, create a KEY(Nome, Sobrenome) must inform in this order. You can WHERE Nome = 'Inkeliz', but cannot use WHERE Sobrenome = 'Lima'. You must necessarily follow the order, i.e. WHERE Nome = 'Inkeliz' AND Sobrenome = 'Lima'For example.If you create a Range, for example, WHERE Nome LIKE 'ANA%' AND Sobrenome = 'Lima', the indexes will be used to find Ana, but will not be used to find the last names, this will still create a temporary table. This indicates that you prefer should put X > 1, BETWEEN 1 AND 5 and LIKE as a last condition, i.e. all equality must come before (including there are "trucks"/"gambiarras" with the IN()), I'll find this later..Problems:Isolate columns:Consider this:SELECT Nome FROM Usuarios WHERE ID + 1 = 10
Did you see the problem? MySQL is not able to make that sum. For us, it's easy to know that ID + 1 = 10 is the same as ID = 9, but MySQL does not do this, as a consequence it will sweep the entire database and add all ID, ignoring any index.More real example:SELECT Nome FROM Acessos WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(UltimoAcesso) <= 7
The same problem, you will run the TO_DAYS() in everything and then subtract and then compare it to see if it is less than 7. Indexes are not miraculous in these cases and will be ignorable.Create Range:In MySQL use CAN IN() be better than BETWEEN, but beware of many conditions, for example:SELECT Nome FROM Usuario WHERE Genero IN ('h', 'm') AND Idade IN (18, 19, 20, 21, 22, 23, 24, 25) AND UltimaAcao > DATE_SUB(NOW(), INTERVAL 7 DAY);
This would have 2*8 possibilities and then would be theoretically better than:SELECT Nome FROM Usuario WHERE Genero IN ('h', 'm') AND Idade BETWEEN 18 AND 25 AND UltimaAcao > DATE_SUB(NOW(), INTERVAL 7 DAY);
This is because using BETWEEN you would generate two ranges the first by Idade and the second per UltimaAcao, while not IN() is equality. This still raises another question, which of the two has greater relevance?! But why use Genero IN ('h', 'm'), assuming they are the only options.The gambiarra of IN() comes by the need to specify all columns, so if you do: KEY(Genero, Pais, Idade) you will always have to inform Genero, Parents and Ages. So we use it IN(), that is if only exists h and m so we do Genero IN ('h', 'm'), in this wayoramos o Genero and we can filter by Parents, or we make a new IN() with all lists of countries and we pass to age. *The last age, again, because it has a potential power to create a Range.If we used:SELECT Nome FROM Usuario WHERE Idade > 18 AND Genero = 'h' AND Pais = 'BR'
I would necessarily ignore the indexes of Genero and Pais, because the first, Idade creates a Range.Relevance and order:Now which has more relevance, consider this:SELECT * FROM Usuario WHERE Ativo = 0 AND id = 10;
SELECT * FROM Usuario WHERE id = 10;
SELECT * FROM Usuario WHERE Ativo IN (0, 1) AND id = 10;
The first and the last could use KEY(Ativo, id), however it would be incompatible with the second. But we could also do:SELECT * FROM Usuario WHERE id = 10 AND Ativo = 0;
SELECT * FROM Usuario WHERE id = 10;
SELECT * FROM Usuario WHERE id = 10 AND Ativo IN (0,1);
So we could use KEY(id, Ativo), they would all use indexes, in this case it tends to be better, because the Active, being binary will return more data than the id which tends to be unique, therefore id has greater relevance and this has great impact.One other thing is to know if you're not going to do this:SELECT * FROM Usuario WHERE id > 10 AND Ativo = 0;
SELECT * FROM Usuario WHERE Ativo = 0;
Because if these queries are used KEY(Ativo, id) could be better, but again depends on the case, so using the Ativo IN() before even if it was only to select the id.Another problem is to create multiple indexes separated by each column, this is terrible and rarely useful.Separate columns:Creating separate indexes can be good if you use:SELECT * FROM tabela WHERE Nome = 'Inkeliz' OR Idade = 18
If you create separate indexes, Nome and Idade MySQL will make a UNION of both results, using each separate index. However do this: SELECT * FROM tabela WHERE Nome = 'Inkeliz' AND Idade = 18
It will not benefit from both indexes, it will only choose to use one of them, not the two. Most often creating divid indexes is not goodIn short.O ORDER BY and GROUP BY also benefits from indexes, however since it does not create a Range, because in this case it falls on filesort even, no way, except making an SSD RAID-0.Indexes are complex, have two books on this, unfortunately do not cover MySQL 5.6 and MySQL 5.7, https://www.xaprb.com/blog/2012/04/01/progress-on-high-performance-mysql-4th-edition/ , they are: http://rads.stackoverflow.com/amzn/click/1449314287 , has a version translated by Altas Books (but it is older yet and there are several complaints of translation failures), in which mentions the use of the gambiarra of IN(), it is not my own (and it a good reduced RAM, depleting repeated indexes). http://rads.stackoverflow.com/amzn/click/B011DBKM0C , practically a book only indexes, this is still in line to read, the book above indicates this too.
Problem with OpenType Palatino Linotype in WPF Annex. On windows 8, the annex works normally, and in windows 7 the upper index is normally displayed. I've noticed that the problem concerns only numbers - the letters are properly displayed in the upper index. I've tried to put the print in the annex, nothing changes. 
