Understanding Mysql execution plan for Update and delete / Select

MySQL & MariaDB
1

Hello Guys

So, I wonder about weird different execution plain that I have been getting in my database Mysql version 5.7 between ( Update / delete and select )

As You can see attached , Whats the difference when We perform an Update or delete using column ( primary key ) I have got hard statements ( full table scan ) and If use the same statement using Join sub query using the same column It works fine ?

Please, look at the screenshot attached

Feel free to ask more details !

image
image1303×628 191 KB

image
image1216×637 226 KB

Thanks in advance,
Andre

2

Hi Andre,

First image is doing an update and the explain is showing “Full Index Scan” which is not same as “full table scan”

The second query is UPDATE with JOIN. May be something like
UPDATE tbl inner join (select tbl where id in (...)) set col=value;

It is fetching records and then checking for update something like this:

for each ID in (inner_sub_query) {
update tbl set col=value where id=ID;
}

Query 1 seems to do the task as needed while in second query we’re using a sub-query to fetch the records to be updated first and then a join to match and update. When you say “it works fine”, you meant second query is faster than the original?

3

Hello Kedar

Yes you are right . The first statement was genereted by framework like Hibernate and It takes a lot of time to process causing a lot of locks because this table has over 700 millions rows however the second one I implemented as workaround and as mention before max time 7seconds and Its enough for us . Both of statments have the same result

I would like to know why the first one performe Index scan ( Primary key ) instead of to seek only the filter id . I confess I never seem this behavior in other engines like Oracle , SQL Server and etc…

Thanks for your help