maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

Hi Roberto

1) Can't you use a relational table? (obvious suggestion, so probably the answer is no. but since MariaDB is relational, this is the main source of your troubles...)

2) If you need 1 row, why should an application send a query like that? It should send the simple SELECT first, and only if no row is found, it should try the non-optimizable SELECTs. If (for some reason I cannot know) this requires too many changes to the application, a simple stored procedure could do the same thing.

3) In case you are using MariaDB 10.0, did you check if this optimization is used?
https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-optimizations/filesort-with-small-limit-optimization/

Tchau!
Federico


--------------------------------------------
Mar 12/8/14, Roberto Spadim <roberto@xxxxxxxxxxxxx> ha scritto:

 Oggetto: [Maria-discuss] optimization
 A: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 Data: Martedì 12 agosto 2014, 22:19
 
 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_typetable
 typepossible_keys
 keykey_len
 refrows
 Extra1
 PRIMARYmov_documentos
 rangePRIMARY
 PRIMARY77
 1
 Using where; Using index2
 UNIONmov_documentos
 rangePRIMARY
 PRIMARY77
 625612
 Using where; Using index3
 UNIONmov_documentos
 rangePRIMARY
 PRIMARY77
 625612
 Using where; Using index4
 UNIONmov_documentos
 index
 PRIMARY77
 1798490
 Using where; Using index5
 UNIONmov_documentos
 index
 PRIMARY77
 1798490
 Using where; Using index6
 UNIONmov_documentos
 index
 PRIMARY77
 1798490
 Using where; Using index
 UNION RESULT<union1,2,3,4,5,6>
 ALL
 
 
 
 
 -- 
 Roberto Spadim
 -----Segue allegato-----
 
 _______________________________________________
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp