guys, i have some queries like this:
SELECT doc_hash_key FROM mov_documentos WHERE doc_hash_key LIKE "j;1;$;294007/3nfe-1%"
UNION
SELECT doc_hash_key FROM mov_documentos WHERE doc_hash_key LIKE "j;1;%;294007/3nfe-1%"
UNION
SELECT doc_hash_key FROM mov_documentos WHERE doc_hash_key LIKE "j;1;%;%294007/3nfe-1%"
UNION
SELECT doc_hash_key FROM mov_documentos WHERE doc_hash_key LIKE "%;%;$;%294007/3nfe-1%"
UNION
SELECT doc_hash_key FROM mov_documentos WHERE doc_hash_key LIKE "%;%;%;294007/3nfe-1%"
UNION
SELECT doc_hash_key FROM mov_documentos WHERE doc_hash_key LIKE "%;%;%;%294007/3nfe-1%"
LIMIT 1
it's a search about a document number, but the problem is....
the first query at first line (with % at end of the query) return really fast, but executing others queries are very slow, the question is
could we optimize this kind of query, since limit=1 row?
the explain of this query:
| id |
select_type | table |
type | possible_keys |
key | key_len |
ref | rows |
Extra |
|---|
| 1 |
PRIMARY | mov_documentos |
range | PRIMARY |
PRIMARY | 77 |
| 1 |
Using where; Using index |
| 2 |
UNION | mov_documentos |
range | PRIMARY |
PRIMARY | 77 |
| 625612 |
Using where; Using index |
| 3 |
UNION | mov_documentos |
range | PRIMARY |
PRIMARY | 77 |
| 625612 |
Using where; Using index |
| 4 |
UNION | mov_documentos |
index | |
PRIMARY | 77 |
| 1798490 |
Using where; Using index |
| 5 |
UNION | mov_documentos |
index | |
PRIMARY | 77 |
| 1798490 |
Using where; Using index |
| 6 |
UNION | mov_documentos |
index | |
PRIMARY | 77 |
| 1798490 |
Using where; Using index |
|
UNION RESULT | <union1,2,3,4,5,6> |
ALL | |
| |
| |
|
--
Roberto Spadim