Need advice small sql delete query

Saahib

@mtoledoce said:
wow! how many people you manage ahh ..if accessID is a code and not a unique identifier for the record.
Well, you are doing something really bad, you can't question tableA with the same data over and over again.

Mentioned already, I don't have direct control over it. Someone else decided that design.

mtoledoce

if they need tableB as log of deleted records then you need tableC for this process or you may die in the intent

netomx

@Saahib said:

@Falzo said:
PS: sorry to have you scared for a moment, that definitely wasn't my intention... was just probing around to find the right setup and reproduce the problem after all ;-)

Still its an issue, I think problem is somewhere else.. I am also using SELECT to see JOINS for test, taking ages.. even selecting with limit 500 is taking about 1 minute 20 seconds ... something is not good.

Btw, just to clarify, all numbers in tableA are unique, they are only 10 digit long , same with tableB, the DB was designed by someone else so can't change its layout, they are using varchar to store those numbers though, if that makes any difference.

iowait ?

Saahib

@mtoledoce said:
if they need tableB as log of deleted records then you need tableC for this process or you may die in the intent

Nope, its not log of deleted records from tableA, its more like a log of records to be deleted from tableA.

Saahib

@netomx said:

iowait ?

iostat when running the query :

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
      0.21    0.04    0.09    0.02    0.00   99.64

Falzo

trying to clarify:

Eobble said: Wouldn't he need a inner join instead of left join for his purpose?

foremost it is simply faster to join the smaller table on the right side of the bigger table, hence why using LEFT JOIN , besides using this to filter out only matching entries.

Saahib said: Btw, just to clarify, all numbers in tableA are unique, they are only 10 digit long

hmm, just to have that clarified, those accessIDs are occuring totally and overall only ONCE in the whole table A?

(that would be more like my first setup which proved the left join to be very fast)

I did try a somewhat other approach, consisting of concatinating the whole table B into one single string with GROUP_CONCAT to search for accessIDs in this string without the need to run a subquery select against table B every time.

SET GLOBAL group_concat_max_len = 10000
SET @myids = ( SELECT GROUP_CONCAT ( accessID ) FROM tableB )
DELETE FROM tableA WHERE FIND_IN_SET(accessID, @myids)

(I omitted the semicolon from every line, as cloudflare would not let me post it otherwise)

this took around 75 seconds to erase around 400k entries from table A ...

yet you have to note that you need to raise group_concat_max_len even more to gather all entries on table B which for sure will increase time taken for the delete againby factors.

25k of 10 digit entries will need >250k as limit... and raising this only worked global for me, which probably wont be the case in every case as you may not have be granted permissions to do so...

actually I am out of further ideas, haven't tried or measured the table copy process suggested by @elgs but as it does also check every single row from table A against a subquery asking for the accessID in table B I'd guess it won't be faster after all.

Saahib

Thanks for your time @Falzo, really appreciate it.

So, I looked into lots of things, CPU time, disk io, even tried ram disk.. however, solution came when I just added index to tableA.accessID and bam.. same query finished in 0.29 sec. Earlier had added to tableB but had no effect as I remember because it was the first thing I did.

Feeling duped and stupid as from last 3-4 days, was not able to think of anything else apart of this issue..

elgs

@Falzo said:
actually I am out of further ideas, haven't tried or measured the table copy process suggested by @elgs but as it does also check every single row from table A against a subquery asking for the accessID in table B I'd guess it won't be faster after all.

INSERT in Mysql is much faster than DELETE. This is the normal way we use to remove part of data from large data set, especially removing the tailing data on a rolling widow of a period of time, a year, let's say. As the OP is using MyISAM, rather than Innodb, ACID is not applicable for MyISAM. There is no reason not to give a try.

IN is slowest, IN should be replaced by EXISTS at least.

Falzo

elgs said: NSERT in Mysql is much faster than DELETE.

yes I agree on that, but the thing that may break the times spend on running the whole is the subquery itself, which has to be run for every row from the large table. even if that small select is done in 0.001 seconds it will add up with 500k rows ;-)

Saahib said: however, solution came when I just added index to tableA.accessID and bam.. same query finished in 0.29 sec.

I'll go and verify that later, yet I can say that this most likely may be the cause, as I also removed the index on my table at some point while trying to reproduce your issues...

glad you found a way to fix it though. kind of a weird thing but good example about how important correct use of indizes is...

Saahib

@Falzo said:

glad you found a way to fix it though. kind of a weird thing but good example about how important correct use of indizes is...

Yes , and it also refreshed some of my know-how about mysql I did years back..