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@spadim.com.br>:
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/quer...
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
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle