← Back to team overview

maria-discuss team mailing list archive

optimization

 

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:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY
mov_documentosrangePRIMARYPRIMARY771Using where; Using index2UNION
mov_documentosrangePRIMARYPRIMARY77625612Using where; Using index3UNION
mov_documentosrangePRIMARYPRIMARY77625612Using where; Using index4UNION
mov_documentosindexPRIMARY771798490Using where; Using index5UNION
mov_documentosindexPRIMARY771798490Using where; Using index6UNION
mov_documentosindexPRIMARY771798490Using where; Using indexUNION RESULT
<union1,2,3,4,5,6>ALL

-- 
Roberto Spadim

Follow ups