Pay attention to this text.
1) You should configure indexes for the fields you use in WHERE . The first step is to do a planning and try to find the fields that are taking server time and resources.
2) Using phpMyAdmin, go to Structure.
3) Lower the screen slightly, and in the index table, note the cardinality of your primary key or index. In our example, we have a fairly large table with a primary key of cardinality greater than 100 thousand.
What does this mean? When we query this table, mysql will search each of the 100,000 records one by one.
With the index created correctly, the key cardinality generally needs to be between 50 and 400. It all depends on your application, the volume of data, etc.
In our test, the query took almost 1 minute to execute (without the configured indexes).
4) In phpMyAdmin go to "SQL". To create the index, type:
CREATE INDEX <nome_do_indice> ON <tabela> ( <campo>(<tamanho>) )
in our example:
CREATE INDEX nomedomeuindice ON contador ( prot(2) )
But what does this 'size' actually mean?
Consider that we are creating a size index (5).
If our bank records are:
João Vitor
Paulo Fagundes
José Cruz
João Guilherme
José Silva
Paulo Roberto
João Francisco
Paulo Amorim
By creating a 5-way key, mysql will organize it as follows:
Whenwedoaquery:SELECT*FROMcadastroWHEREnome=“JoãoVitor”,insteadofMySQLscrollingthroughall8recordsinourexampletable,itwillonlycheckthe3indexeswecreated.
Asshowninourvideo,thisoptimizationwithinatablewithmanyrecordswillsaveyoualotoftimeandprocessing!
Applyingindexesinourdatabasewithmorethan100,000records,reducedquerytimefromalmost1minutetolessthan0.01seconds!
HowdoIfindoutwhichfieldtoindex?
JustrunyourqueriesinphpMyAdminbyplacing"desc" before. Mysql will show you step by step the path he is going through to give you a result.
Example:
desc SELECT * FROM 'contador' WHERE prot=’03554d6b’
Result:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE contador ref nomedomeuindice nomedomeuindice 4 const 462 Using where
Source
