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?
MariaDB selects are fast, but a delete query as above required 1.25
minutes in MariaDB, but 0.3 seconds in SQLite (identical
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
Apparently the same issues occur in PostgreSQL.