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):
PRIMARY => `id` guideid => `guideid`, `orderNumber` guideid_2 => `guideid` orderNumber => `orderNumber`
PRIMARY => `id` guideid => `guideid`, `orderNumber` temp_usNum => `temp_usNum` orderNumber => `orderNumber` selected_number => `selected_number` guideid_2 => `guideid` itemid => `itemid` itemid_2 => `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.