[Maria-discuss] When do Query Select to Big Table is too slow !
Hi. I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table. When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying. I do this specific query: /select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;// / I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER" Regards! -- AngelHanielCantuJauregui angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business;> Gerencia | Servicios de Innovacion para Empresas SA de CV Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones (818) 421 9924 <tel:(818) 421 9924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business> https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/> <https://www.moneybox.business>
Hi Angel! May I suggest you also post the EXPLAIN output for the query that is causing problems? EXPLAIN select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12; It seems like this table is the biggest one you have. If you are running MariaDB 10.1 or greater, you can replace ANALYZE with EXPLAIN. This will also run the query and show how many rows were scanned compared to the expected number of scanned rows, in addition to providing the query plan. Come back with that information and I might be able to take you in the right direction towards fixing this. Vicențiu On Wed, 11 Dec 2019 at 05:32, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi.
I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table.
When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying.
I do this specific query:
*select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;*
I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER"
Regards!
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
_______________________________________________ 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
Hi Vincentiu. Thanks for your comments, I can't now do the query because exists clients using the web app, so I do the query directly on phpmyadmin and this say: /ANALYZE SELECT * FROM FACTURACION WHERE ID_USUARIO='ndbny' AND (TIMBRE_FISCAL!='' OR TIMBRE_FISCAL!='0') AND PAGADA!='1' AND CANCELADO='0' AND FECHA BETWEEN '1514764800' AND '1576073257' ORDER BY FECHA DESC LIMIT 1,15;/ PHPMyAdmin is in the same server where the mariaDB is running, but the web app is in a different server (digital ocean) Regards ! On 12/11/19 1:12 AM, Vicențiu Ciorbaru wrote:
Hi Angel!
May I suggest you also post the EXPLAIN output for the query that is causing problems?
EXPLAIN select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;
It seems like this table is the biggest one you have. If you are running MariaDB 10.1 or greater, you can replace ANALYZE with EXPLAIN. This will also run the query and show how many rows were scanned compared to the expected number of scanned rows, in addition to providing the query plan.
Come back with that information and I might be able to take you in the right direction towards fixing this.
Vicențiu
On Wed, 11 Dec 2019 at 05:32, Ing Angel Cantu <angel.cantu@sie-group.net <mailto:angel.cantu@sie-group.net>> wrote:
Hi.
I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table.
When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying.
I do this specific query:
/select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;// /
I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER"
Regards!
--
AngelHanielCantuJauregui
angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <tel:(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722>
angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business>
20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- AngelHanielCantuJauregui angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business;> Gerencia | Servicios de Innovacion para Empresas SA de CV Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones (818) 421 9924 <tel:(818) 421 9924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business> https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/> <https://www.moneybox.business>
Hi Angel! I suggest you create an index on FACTURACION.ID_USUARIO to start of with. If that does not solve your performance problem, try creating a multi-part one on (ID_USUARIO, FECHA). What surprises me is that r_rows is very small (the number of actual rows read from the table), but the the query optimizer believed it will read many more when doing the query plan. After creating the index, you should see a change in the output of ANALYZE, where the KEY used is ID_USUARIO. Hope this helps. Vicențiu On Wed, 11 Dec 2019 at 16:50, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi Vincentiu.
Thanks for your comments, I can't now do the query because exists clients using the web app, so I do the query directly on phpmyadmin and this say:
*ANALYZE SELECT * FROM FACTURACION WHERE ID_USUARIO='ndbny' AND (TIMBRE_FISCAL!='' OR TIMBRE_FISCAL!='0') AND PAGADA!='1' AND CANCELADO='0' AND FECHA BETWEEN '1514764800' AND '1576073257' ORDER BY FECHA DESC LIMIT 1,15;*
PHPMyAdmin is in the same server where the mariaDB is running, but the web app is in a different server (digital ocean)
Regards !
On 12/11/19 1:12 AM, Vicențiu Ciorbaru wrote:
Hi Angel!
May I suggest you also post the EXPLAIN output for the query that is causing problems?
EXPLAIN select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;
It seems like this table is the biggest one you have. If you are running MariaDB 10.1 or greater, you can replace ANALYZE with EXPLAIN. This will also run the query and show how many rows were scanned compared to the expected number of scanned rows, in addition to providing the query plan.
Come back with that information and I might be able to take you in the right direction towards fixing this.
Vicențiu
On Wed, 11 Dec 2019 at 05:32, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi.
I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table.
When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying.
I do this specific query:
*select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;*
I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER"
Regards!
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
_______________________________________________ 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
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
An additional thought is that you could alter your tables to use INNODB engine instead of MyISAM, if your application can handle that. It might be that your slow running queries are waiting for locks from other queries updating the table. Innodb will probably provide you with better performance in your use case. Command to change engine ALTER TABLE <table-name> ENGINE=InnoDB On Thu, 12 Dec 2019 at 09:38, Vicențiu Ciorbaru <vicentiu@mariadb.org> wrote:
Hi Angel!
I suggest you create an index on FACTURACION.ID_USUARIO to start of with. If that does not solve your performance problem, try creating a multi-part one on (ID_USUARIO, FECHA).
What surprises me is that r_rows is very small (the number of actual rows read from the table), but the the query optimizer believed it will read many more when doing the query plan.
After creating the index, you should see a change in the output of ANALYZE, where the KEY used is ID_USUARIO.
Hope this helps.
Vicențiu
On Wed, 11 Dec 2019 at 16:50, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi Vincentiu.
Thanks for your comments, I can't now do the query because exists clients using the web app, so I do the query directly on phpmyadmin and this say:
*ANALYZE SELECT * FROM FACTURACION WHERE ID_USUARIO='ndbny' AND (TIMBRE_FISCAL!='' OR TIMBRE_FISCAL!='0') AND PAGADA!='1' AND CANCELADO='0' AND FECHA BETWEEN '1514764800' AND '1576073257' ORDER BY FECHA DESC LIMIT 1,15;*
PHPMyAdmin is in the same server where the mariaDB is running, but the web app is in a different server (digital ocean)
Regards !
On 12/11/19 1:12 AM, Vicențiu Ciorbaru wrote:
Hi Angel!
May I suggest you also post the EXPLAIN output for the query that is causing problems?
EXPLAIN select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;
It seems like this table is the biggest one you have. If you are running MariaDB 10.1 or greater, you can replace ANALYZE with EXPLAIN. This will also run the query and show how many rows were scanned compared to the expected number of scanned rows, in addition to providing the query plan.
Come back with that information and I might be able to take you in the right direction towards fixing this.
Vicențiu
On Wed, 11 Dec 2019 at 05:32, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi.
I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table.
When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying.
I do this specific query:
*select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;*
I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER"
Regards!
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
_______________________________________________ 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
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
Hi Vicentiu. I understood that MyISAM is better than InnoDB in the speed of insertion, which is why we prefer MyISAM since the REST API web service is used for insertion processes that are much greater than queries. But if InnoDB is much more better, I change it ! Regards ! On 12/12/19 1:43 AM, Vicențiu Ciorbaru wrote:
An additional thought is that you could alter your tables to use INNODB engine instead of MyISAM, if your application can handle that. It might be that your slow running queries are waiting for locks from other queries updating the table. Innodb will probably provide you with better performance in your use case.
Command to change engine ALTER TABLE <table-name> ENGINE=InnoDB
On Thu, 12 Dec 2019 at 09:38, Vicențiu Ciorbaru <vicentiu@mariadb.org <mailto:vicentiu@mariadb.org>> wrote:
Hi Angel!
I suggest you create an index on FACTURACION.ID_USUARIO to start of with. If that does not solve your performance problem, try creating a multi-part one on (ID_USUARIO, FECHA).
What surprises me is that r_rows is very small (the number of actual rows read from the table), but the the query optimizer believed it will read many more when doing the query plan.
After creating the index, you should see a change in the output of ANALYZE, where the KEY used is ID_USUARIO.
Hope this helps.
Vicențiu
On Wed, 11 Dec 2019 at 16:50, Ing Angel Cantu <angel.cantu@sie-group.net <mailto:angel.cantu@sie-group.net>> wrote:
Hi Vincentiu.
Thanks for your comments, I can't now do the query because exists clients using the web app, so I do the query directly on phpmyadmin and this say:
/ANALYZE SELECT * FROM FACTURACION WHERE ID_USUARIO='ndbny' AND (TIMBRE_FISCAL!='' OR TIMBRE_FISCAL!='0') AND PAGADA!='1' AND CANCELADO='0' AND FECHA BETWEEN '1514764800' AND '1576073257' ORDER BY FECHA DESC LIMIT 1,15;/
PHPMyAdmin is in the same server where the mariaDB is running, but the web app is in a different server (digital ocean)
Regards !
On 12/11/19 1:12 AM, Vicențiu Ciorbaru wrote:
Hi Angel!
May I suggest you also post the EXPLAIN output for the query that is causing problems?
EXPLAIN select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;
It seems like this table is the biggest one you have. If you are running MariaDB 10.1 or greater, you can replace ANALYZE with EXPLAIN. This will also run the query and show how many rows were scanned compared to the expected number of scanned rows, in addition to providing the query plan.
Come back with that information and I might be able to take you in the right direction towards fixing this.
Vicențiu
On Wed, 11 Dec 2019 at 05:32, Ing Angel Cantu <angel.cantu@sie-group.net <mailto:angel.cantu@sie-group.net>> wrote:
Hi.
I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table.
When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying.
I do this specific query:
/select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;// /
I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER"
Regards!
--
AngelHanielCantuJauregui
angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <tel:(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722>
angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business>
20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net <mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
--
AngelHanielCantuJauregui
angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <tel:(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722>
angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business>
20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/>
-- AngelHanielCantuJauregui angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business;> Gerencia | Servicios de Innovacion para Empresas SA de CV Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones (818) 421 9924 <tel:(818) 421 9924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business <mailto:angel.cantu@moneybox.business> https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico facebook <https://business.facebook.com/moneyboxco/> twitter <https://twitter.com/moneyboxlatam/> linkedin <https://www.linkedin.com/company/27011448/> instagram <https://www.instagram.com/moneyboxlatam/> <https://www.moneybox.business>
MyISAM is probably faster when doing inserts (append only), but for any sort of concurrent updates & reads, this might cause readers to wait for writers to finish. I first suggest you add the index, then if that doesn't work, try switching storage engines and add the index as well. On Fri, 13 Dec 2019 at 02:44, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi Vicentiu.
I understood that MyISAM is better than InnoDB in the speed of insertion, which is why we prefer MyISAM since the REST API web service is used for insertion processes that are much greater than queries.
But if InnoDB is much more better, I change it !
Regards ! On 12/12/19 1:43 AM, Vicențiu Ciorbaru wrote:
An additional thought is that you could alter your tables to use INNODB engine instead of MyISAM, if your application can handle that. It might be that your slow running queries are waiting for locks from other queries updating the table. Innodb will probably provide you with better performance in your use case.
Command to change engine ALTER TABLE <table-name> ENGINE=InnoDB
On Thu, 12 Dec 2019 at 09:38, Vicențiu Ciorbaru <vicentiu@mariadb.org> wrote:
Hi Angel!
I suggest you create an index on FACTURACION.ID_USUARIO to start of with. If that does not solve your performance problem, try creating a multi-part one on (ID_USUARIO, FECHA).
What surprises me is that r_rows is very small (the number of actual rows read from the table), but the the query optimizer believed it will read many more when doing the query plan.
After creating the index, you should see a change in the output of ANALYZE, where the KEY used is ID_USUARIO.
Hope this helps.
Vicențiu
On Wed, 11 Dec 2019 at 16:50, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi Vincentiu.
Thanks for your comments, I can't now do the query because exists clients using the web app, so I do the query directly on phpmyadmin and this say:
*ANALYZE SELECT * FROM FACTURACION WHERE ID_USUARIO='ndbny' AND (TIMBRE_FISCAL!='' OR TIMBRE_FISCAL!='0') AND PAGADA!='1' AND CANCELADO='0' AND FECHA BETWEEN '1514764800' AND '1576073257' ORDER BY FECHA DESC LIMIT 1,15;*
PHPMyAdmin is in the same server where the mariaDB is running, but the web app is in a different server (digital ocean)
Regards !
On 12/11/19 1:12 AM, Vicențiu Ciorbaru wrote:
Hi Angel!
May I suggest you also post the EXPLAIN output for the query that is causing problems?
EXPLAIN select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;
It seems like this table is the biggest one you have. If you are running MariaDB 10.1 or greater, you can replace ANALYZE with EXPLAIN. This will also run the query and show how many rows were scanned compared to the expected number of scanned rows, in addition to providing the query plan.
Come back with that information and I might be able to take you in the right direction towards fixing this.
Vicențiu
On Wed, 11 Dec 2019 at 05:32, Ing Angel Cantu <angel.cantu@sie-group.net> wrote:
Hi.
I have problems to make query to a table from the db, is delaying so much (too slow), I don't know whats I need to do, I do some test to my code but is not the problem, is the table.
When I make a select to the table marked with red, the response delay 6 or 8 seconds, but, if I do a query to another table the response delay 1 second (fast). Only the table marked with red is delaying.
I do this specific query:
*select * from FACTURACION where ID_USER='ID_HASH' order by SERVER_DATE limit 1,12;*
I only query the first 12 results order by date, and in the code I make the pagination using "?page=NUMBER"
Regards!
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
_______________________________________________ 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
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
<https://www.moneybox.business>
-- Angel Haniel Cantu Jauregui
angel.cantu@moneybox.business <angel.cantu@moneybox.business;>
Gerencia | Servicios de Innovacion para Empresas SA de CV
Proveemos soluciones para facilitar la administración de tu negocio y garantizar tus próximas vacaciones en compañia de tu familia sin preocupaciones
(818) 421 9924 <(818)%20421%209924> | (899) 871 1722 <https://wa.me/528998711722> angel.cantu@moneybox.business https://www.moneybox.business 20 de noviembre #300, CP 88780 Reynosa, Tamps, Mexico
[image: facebook] <https://business.facebook.com/moneyboxco/> [image: twitter] <https://twitter.com/moneyboxlatam/> [image: linkedin] <https://www.linkedin.com/company/27011448/> [image: instagram] <https://www.instagram.com/moneyboxlatam/>
participants (2)
-
Ing Angel Cantu -
Vicențiu Ciorbaru