maria-discuss team mailing list archive

[Vicențiu Ciorbaru <vicentiu@xxxxxxxxxxx>]

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@xxxxxxxxxxxxx>
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@xxxxxxxxxxx>
> 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@xxxxxxxxxxxxx>
>> 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@xxxxxxxxxxxxx>
>>> 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@xxxxxxxxxxxxxxxxxxx
>>>> 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/>
>
> <https://www.moneybox.business>
>
>