Solved: [MYSQL]: Delete is very slow

Create Account

MySQL Server

Questions

Followers

Top Experts

Raf

[MYSQL]: Delete is very slow

Hi experts,

I have to delete a table using this statement:

delete FROM <tablename> where start_date like '%YYYY-MM%'; (over 1000000 rows)

but it's taking quite a long time.

I'd like to create a stored procedure that does a delete with intermediate commits ever 1000 records.

Have You any idea?

Thanks in advance!

Regards

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

ASKER CERTIFIED SOLUTION

Chris Stanyon🇬🇧

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Pawan Kumar🇮🇳

Use like..

DELETE in batches... Create a loop ... In every iteration DELETE 500 records..

Tomas Helgi Johannsson🇮🇸

Hi!

Is the column start_date in an index ?
Also look at this

where start_date like '%YYYY-MM%';

Open in new window

the % sign before YYYY tells the database to look at all dates regardless of the year. I presume that start_date is a date field or string field of date/timestamp/datetime values in the format 'YYYY-MM-DD' / 'YYYY-MM-DD HH:MM:SS' .
If that is the case then you can skip the % in the front of your search string like this 'YYYY-MM%'.
Doing this and having start_date column in an index will speed up this delete statement.

Regards,
Tomas Helgi

SOLUTION

skullnobrains

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

MySQL Server

Questions

Followers

Top Experts

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.