UPDATE query with JOIN taking forever
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%';