[Maria-discuss] optimization
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: idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY mov_documentosrangePRIMARYPRIMARY771Using where; Using index2UNION mov_documentosrangePRIMARYPRIMARY77625612Using where; Using index3UNION mov_documentosrangePRIMARYPRIMARY77625612Using where; Using index4UNION mov_documentosindexPRIMARY771798490Using where; Using index5UNION mov_documentosindexPRIMARY771798490Using where; Using index6UNION mov_documentosindexPRIMARY771798490Using where; Using indexUNION RESULT <union1,2,3,4,5,6>ALL -- Roberto Spadim
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
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 <javascript:;>> ha scritto:
Oggetto: [Maria-discuss] optimization A: "Maria Discuss" <maria-discuss@lists.launchpad.net <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@lists.launchpad.net <javascript:;> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
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
Hi, Roberto! On Aug 12, Roberto Spadim wrote:
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?
Sounds very reasonable. And, I hope, not too difficult to implement. Could you please put this in Jira? Regards, Sergei
:) yes no problem 2014-09-04 13:47 GMT-03:00 Sergei Golubchik <serg@mariadb.org>:
Hi, Roberto!
On Aug 12, Roberto Spadim wrote:
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?
Sounds very reasonable. And, I hope, not too difficult to implement. Could you please put this in Jira?
Regards, Sergei
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
insert into jira (id) value ('MDEV-6696') =] https://mariadb.atlassian.net/browse/MDEV-6696 thanks sergei 2014-09-04 13:57 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
:) yes no problem
2014-09-04 13:47 GMT-03:00 Sergei Golubchik <serg@mariadb.org>:
Hi, Roberto!
On Aug 12, Roberto Spadim wrote:
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?
Sounds very reasonable. And, I hope, not too difficult to implement. Could you please put this in Jira?
Regards, Sergei
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
participants (3)
-
Federico Razzoli -
Roberto Spadim -
Sergei Golubchik