Re: Slow deletes (MariaDB)

On 8/20/2021 2:00 PM, Jerry Stuckle wrote:
> On 8/20/2021 1:34 PM, DFS wrote:
>> Deletes of this type are very slow:
>>
>> delete from childtbl
>> where id1 in
>> (
>>    select id1
>>    from parenttbl
>>    where id2 in
>>    (
>>     select id2
>>     from othertbl
>>     where condition
>>    )
>> )
>>
>>
>> Like 1.5 minutes to delete a couple thousand rows.
>>
>> How can I speed them up?
>>
>>
> 
> How big are your tables?  

In this case:
parenttbl nearly 3M rows
childtbl  nearly 3M rows
othertbl  130K rows


> Do you have indexes on id1 and id2 in their 
> appropriate tables?

Yes.

MariaDB selects are fast, but a delete query as above required 1.25 
minutes in MariaDB, but 0.3 seconds in SQLite (identical 
SQL/tables/structures/indexes/starting rowcounts).

Depending on the table, select count(*) in MariaDB is also agonizingly 
slow: eg 1.67 minutes vs 1 second in SQLite for a table of 3 int columns 
(nearly 15M rows)

SQLite is no MariaDB, I understand that, but geez.

I researched the slow MariaDB DELETE and row counting, and there're no 
easy answers.

https://www.google.com/search?q=mariadb+slow+delete

Apparently the same issues occur in PostgreSQL.