maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

about this filesort with small limit
that's not the case, i don't have a order by, just a list of queries that
return (or not) some rows


2014-08-12 18:28 GMT-03:00 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> 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
>>
>>
>
> --
> Roberto Spadim
> SPAEmpresarial
> Eng. Automação e Controle
>
>


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