Optimize search in Mysql

Question

Hello I have a query that I can’t optimize.

Records: 1.904.447 records
Name: table_mae

Related to
Records: 10.748.360 records
Name: table_son

-- index criado para id_tabela_mae
-- index criado para data
SELECT tabela_mae.nome FROM tabela_mae INNER JOIN tabela_filho ON 
tabela_mae.id = tabela_filho.id_tabela_mae AND DATE(tabela_filho.data) 
BETWEEN  '2015-06-09' AND '2015-06-09' ORDER BY tabela_mae.flag_fechou,
tabela_mae.data_fechou ASC

This command takes more than 20 seconds to execute if you take the ORDER BY, takes 10 seconds.

Apparently the problem is that in between have to scan the whole table to find few records.

Answer 1

Pay attention to this text.

1) You must configure indexes for the fields you use in WHERE. The first step is to make a plan and try to find the fields that are taking time and resources from the server.

2) Using phpMyAdmin, go to Structure.

3) Scroll down the screen a little, 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,000.

What does this mean? When we query this table, mysql will search in each of the 100,000 records, one by one.

With the index created correctly, in general, the key cardinality needs to be between 50 and 400. It all depends on your application, data volume, etc.

In our test, the query took almost 1 minute to run (without 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).

In case our bank records are:

João Vitor
Paulo Fagundes
José Cruz
John William
José Silva
Paulo Roberto
João Francisco
Paulo Amorim

Creating a key with 5 positions, mysql will arrange as follows:

When we query: SELECT * FROM cadastro WHERE nome = “João Vitor” , instead of Mysql going through all 8 records of our table-example, it will only check the 3 indexes we created.

As shown in our video, this optimization within a table with many records, will save you a lot of time and processing!

The application of indexes in our database with more than 100,000 records, reduced the query time from almost 1 minute to less than 0.01 seconds !

How do I find out which field I need to index?

Just run your queries in phpMyAdmin by putting "desc" first. mysql will show you step by step the path it is going through to provide you with a result.

Example:

desc SELECT * FROM `contador` WHERE prot=’03554d6b’

Upshot:

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