maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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@xxxxxxxx> 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@xxxxxxxxxxxxx <javascript:;>> ha
> scritto:
>
>  Oggetto: [Maria-discuss] optimization
>  A: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx <javascript:;>>
>  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 <javascript:;>
>  Unsubscribe : https://launchpad.net/~maria-discuss
>  More help   : https://help.launchpad.net/ListHelp
>
>

-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle