I'm runnning 10.0.12 but the applocation is closed source. I can't fix that

I will try to upgrade to 10.0.13 or 10.1.0 and check if this optimizations is bein used but i think not aince if i execute each query it return fast

About table engine it's using aria

Em terça-feira, 12 de agosto de 2014, Federico Razzoli <federico_raz@yahoo.it> escreveu:

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@spadim.com.br> ha scritto:

 Oggetto: [Maria-discuss] optimization
 A: "Maria Discuss" <maria-discuss@lists.launchpad.net>
 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@lists.launchpad.net
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp

--

Roberto Spadim
SPAEmpresarial

Eng. Automação e Controle