Pesquisa lenta

[t0th]

oLÁ,

tenho a seguinte situação:

mysql> show columns from categorias;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| nome | varchar(40) | YES | | NULL | |

+-------+------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

mysql> show index from categorias;

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| categorias | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | |

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

1 row in set (0.00 sec)

mysql> show columns from lojas;;

+---------------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| categorias_id | int(10) unsigned | NO | MUL | 0 | |

| nome | varchar(60) | YES | | NULL | |

| site | varchar(60) | YES | | NULL | |

| logo | varchar(50) | YES | | NULL | |

| lista | varchar(100) | YES | | NULL | |

+---------------+------------------+------+-----+---------+----------------+

6 rows in set (0.00 sec)

mysql> show index from lojas;;

+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

| lojas | 0 | PRIMARY | 1 | id | A | 16 | NULL | NULL | | BTREE | |

| lojas | 1 | categorias_id | 1 | categorias_id | A | 8 | NULL | NULL | | BTREE | |

+-------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.01 sec)

mysql> show columns from produtos;

+----------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| lojas_id | int(10) unsigned | NO | MUL | 0 | |

| nome | varchar(60) | YES | | NULL | |

| codigo | varchar(15) | YES | | NULL | |

| preço | varchar(10) | YES | | NULL | |

| descc | text | YES | | NULL | |

| foto | varchar(200) | YES | | NULL | |

+----------+------------------+------+-----+---------+----------------+

7 rows in set (0.00 sec)

mysql> show index from produtos;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| produtos | 0 | PRIMARY | 1 | id | A | 26840 | NULL | NULL | | BTREE | |

| produtos | 1 | lojas_id | 1 | lojas_id | A | 18 | NULL | NULL | | BTREE | |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT sql_cache produtos.nome,produtos.preço,lojas.nome as nomeLoja, site, produtos.id FROM produtos inner join lojas on lojas.id = produtos.lojas_id where ( produtos.nome LIKE '%processador%' or produtos.nome LIKE '%cpu%' or produtos.nome LIKE '%PROC %' or produtos.nome LIKE '%PROC. %' or produtos.nome LIKE '%PROCESADOR %') ORDER BY produtos.nome asc limit 0,30;

+----+-------------+----------+--------+---------------+---------+---------+------------------------------------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+--------+---------------+---------+---------+------------------------------------+-------+-----------------------------+

| 1 | SIMPLE | produtos | ALL | lojas_id | NULL | NULL | NULL | 27560 | Using where; Using filesort |

| 1 | SIMPLE | lojas | eq_ref | PRIMARY | PRIMARY | 4 | lojasnoparaguai1.produtos.lojas_id | 1 | |

+----+-------------+----------+--------+---------------+---------+---------+------------------------------------+-------+-----------------------------+

2 rows in set (0.00 sec)

a TAbela produtos tem 27mil registro..

existe alguma maneira de optimizar essa consulta ou essas tabelas?

desde já agradeço..

muito obrigado

[Denis Courcy]

Oi, 't0th'!

CREATE TABLE produtos (
   id int(10) unsigned not NULL auto_increment,
   lojas_id  int(10) unsigned NO NULL default 0,
   nome varchar(60),
   codigo varchar(15),
   preço varchar(10),
   descc text,
   foto varchar(200) 
   primary key(id),
   index key ( lojas_id )
);

EXPLAIN SELECT sql_cache produtos.nome,produtos.preço,lojas.nome as nomeLoja, site, produtos.id 
FROM produtos 
inner join lojas on lojas.id = produtos.lojas_id 
where ( produtos.nome LIKE '%processador%' or produtos.nome LIKE '%cpu%' or produtos.nome LIKE '%PROC %' or produtos.nome LIKE '%PROC. %' or produtos.nome LIKE '%PROCESADOR %') 
ORDER BY produtos.nome asc 
limit 0,30;

a TAbela produtos tem 27mil registro.. existe alguma maneira de optimizar essa consulta ou essas tabelas? desde já agradeço.. muito obrigado

Explicação do problema: Quando você usa o LIKE com o simbolo de % no início e no final, tal como você está fazendo em toda sua pesquisa, ('%processador%', por exemplo) você está obrigando o gerenciador de banco de dados (qualquer um, seja SQL Server, MySQL, ORACLE, etc) a operar por TABLE SCAN, que é uma varredura de registro a registro do início ao fim da tabela. Mesmo se você tivesse íncdice por este campo o TABLE SCAN continuaria a ser executado. Solução: Se o que você quer achar está nas primeiras letras do nome você pode criar um íncdice pelo atributo "nome"

CREATE INDEX produtos_AK_001 ON produtos (nome);

e alterar a cláusula WHERE para que fique assim:

WHERE (produtos.nome LIKE '%proc' or produtos.nome LIKE '%cpu%' or produtos.nome LIKE '%PROC')

Se o que você quer achar está em qualquer lugar do campo "nome", ou seja, tal como está a sua consulta, então crie um indice FULLTEXT pelo campo nome, que faz a busca como o google faz,

CREATE FULLTEXT INDEX produtos_FT_001 ON produtos (nome);

e alterar a cláusula WHERE para que fique assim:

WHERE MATCH (nome) AGAINST ('PROC PROCESSADOR CPU');