UPDATE query with JOIN taking forever I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would save a ton of time if it works.

I have two tables:


items: ~40,000 Rows
inventory: ~1,000,000 Rows


I have a query that matches up the inventory with the items the way the old system did, and updates the itemid column in the inventory table, to the id of the matched row in the items table (to work with the new system).

The problem is that the queries are taking forever, and I have very similar queries that I have to run in order to prioritize similar matches so waiting this amount of time isn't very great as the data in the old database is not consistent at all, so I have to continuously tweak, rerun, tweak, rerun, etc. It was taking a little over 40 minutes per query when there was only 250,000 rows in the inventory table.

In a nutshell, I need to make this query faster:

UPDATE inventory r
JOIN items t ON (FIND_IN_SET(r.orderNumber,
                REPLACE(
                    REPLACE(
                        REPLACE(t.orderNumber,'/',','),
                    ' ', ''),
                '[]',',')
            )
            AND r.selected_number != '99999'
            AND r.guideid = t.guideid
            AND (r.itemid IS NULL OR r.itemid = '')
                 )
SET r.itemid = t.id


My indices are (comma delimited obviously being composite, please excuse the names they were auto-generated by SQLYog):

items table:

PRIMARY =&gt; `id`
guideid =&gt; `guideid`, `orderNumber`
guideid_2 =&gt; `guideid`
orderNumber =&gt; `orderNumber`


inventory table:

PRIMARY =&gt; `id`
guideid =&gt; `guideid`, `orderNumber`
temp_usNum =&gt; `temp_usNum`
orderNumber =&gt; `orderNumber`
selected_number =&gt; `selected_number`
guideid_2 =&gt; `guideid`
itemid =&gt; `itemid`
itemid_2 =&gt; `itemid`, `guideid`, `orderNumber`, `selected_number`


Here is the result of the explain query:



I have access to all server settings, as it is on my local machine.
With your current structure, your query may be doing >1,000,000,000 row scans. This is probably due to the FIND_IN_SET - meaning the column cannot be used in an index, resulting in the optimizer choosing other indexes with very bad selectivity (you have to read 500000 rows instead of 1 million, that is very bad selectivity). The fact that an index is being used does not mean good performance, if it is the wrong index. To understand why, you must know that function(column) = value cannot be indexed directly in MySQL.

I would start by changing the column items.ordernumber and putting that information on a separate table, with only one item per column, instead of the denormalized arrangement you have currently.

With that, and the proper indexes, you will be able to do the query in the items -> inventory direction and use ordernumber to filter adequately your rows.

As a secondary thing, you have many redundant indexes, like (guideid, orderNumber) and (guideid). You are in need of a good cleanup in terms of design.

To measure the number of row operations that you are doing for a particular query, do the following:

FLUSH STATUS;
-- Execute query
SHOW STATUS like 'Hand%';