Mysql very slow Join Performance

Question

I'm hoping someone can advise, but I'm experiencing very long processing times around the following join statement, and was hoping to get some suggestions on improving performance. Both tables have millions of records in each, and I have column indexes on, but it's still taking over 70 hours to run this join.

update <table x> a
left join <table y> b
on a.PARENT_ID=b.ID and a.LOAD_ID=b.LOADID                    
set a.DATETIME=str_to_date(b.`DateTime`, '%m/%d/%Y %H:%i:%s'), a.ROOM_ID=b.ConID, a.STATUS='Exited'
where a.PARENT_ID=b.ID and a.LOAD_ID=b.LOADID and a.PROCESSING_FLAG = 0 and b.PROCESSING_FLAG = 0

So table x has 66m records, and table y has 44m, but using the PROCESSING_FLAG it won't be joining all 66m records to 44m records, only a subset. I have column indexes on a.Parent_Id, b.ID, a.Load_ID, b.LoadID, a.Room_ID, b.ConID, a.Processing_Flag, and b.Processing_Flag. Both tables use MYISAM and I'm using MySQL 5.6.17.

Is there anyhting I can do to improve the performance of this statement? I was thinking of increasing the key buffer perhaps to 6G as a first step.

Did you try running it through EXPLAIN to see if it is actually using the indexes you think it is? — Brian Glaz
PROCESSING_FLAG is apparently 0 or 1, therefore its cardinality is useless - whether you index that or not, MySQL will still go trough entire data set to determine certain records aren't needed. I don't see a single satisfactory criteria for efficient index use in that query. Next silly thing is that you're using MyISAM for some odd reason, so you're I/O bound since RAM is most likely not used to store hot copy of working data set. I doubt this can be optimized whatsoever without using a more advanced storage engine that can utilise RAM efficiently. — N.B.
I haven't yet as the statement is still running, and there locked the tables. — user1236443
Yes Processing flag is set as a bit so either 1 or 0. The idea is that I get data in chucks, and each chuck gets processed. So I wanted a way of only new data being processed and not the old stuff. — user1236443
Using MYISAM because I read somewhere that it's faster. I don't use Innodb because this is not an application so don't need primary and foreign keys and do need ACID compliance. I tend to get denormalised data. — user1236443

PankajR PankajR · Accepted Answer · 2014-08-28T15:02:01

MYISAM is very best to Select operation. if your database is very much heavy with good user . Update operation will take time.

try to make join with index column. Join will perform good.

communicate with your mysql server admin tell him/her to increase join_buffer_size

join_buffer_size server variable to responsible allocate memory for joins that not perform join with index column

I hope this will help you.

Mysql very slow Join Performance

1 Answers