1

I am running MySQL InnoDB 5.6. This is my first post, so please bare with me of any mistakes.

I have this schema: Database schema

I have a list of invoices in the invoice table, and if I want to see all the materials tied to specific invoice I join the invoice table with the invoice_materials table. The problem is I maintain an audit trail of the material_list table into change_log, and the material_list table always contains the most up to date material properties. Now when I get my list of invoice materials it does not show how that material was on the date the invoice was. To obtain how the material was on the date of the invoice creation I perform this query:

SELECT invoice_materials.invoice_materials_id,
invoice.invoice_number AS Invoice_number,
    IFNULL((SELECT our_material_codes.name FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    LEFT JOIN our_material_codes ON (our_material_codes.our_material_code_id = change_log.old_value)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'our_material_code_id'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), our_material_codes.name
    ) 
AS Our_Material_code,
    IFNULL((SELECT vendors.vendor FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    LEFT JOIN vendor_material_names ON (vendor_material_names.vendor_material_names_id = change_log.old_value)
    LEFT JOIN vendors ON (vendors.vendor_id = vendor_material_names.vendor_id)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'vendor_material_names_id'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), vendors.vendor)
AS Vendor,
    IFNULL((SELECT vendor_material_names.description FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    LEFT JOIN vendor_material_names ON (vendor_material_names.vendor_material_names_id = change_log.old_value)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'vendor_material_names_id'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), vendor_material_names.description)
AS Material_description, 
    IFNULL((SELECT vendor_material_names.part_number FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    LEFT JOIN vendor_material_names ON (vendor_material_names.vendor_material_names_id = change_log.old_value)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'vendor_material_names_id'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), vendor_material_names.part_number) AS Part_number,
invoice_materials.quantity,
    IFNULL((SELECT material_uom.uom FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    LEFT JOIN material_uom ON (material_uom.material_uom_id = change_log.old_value)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'material_uom_id'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), material_uom.uom)
AS UOM, 
    IFNULL((SELECT change_log.old_value FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'unit_price'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), material_list.unit_price) 
AS Unit_price,
    IFNULL((SELECT change_log.old_value FROM change_log 
    LEFT JOIN table_columns ON (change_log.column_id = table_columns.table_columns_id)
    LEFT JOIN customer ON (customer.cust_id = change_log.old_value)
    WHERE row_id = material_list.material_list_id 
    AND change_log.table_id = 3661
    AND table_columns.column_name = 'cust_id'
    AND change_log.created_time >= invoice_materials.created_time
    ORDER BY change_log.created_time ASC LIMIT 1), customer.name) AS Customer,
invoice_materials.total_price AS Total_price
FROM invoice_materials
LEFT JOIN invoice ON (invoice.invoice_id = invoice_materials.invoice_id)
LEFT JOIN material_list ON (material_list.material_list_id = invoice_materials.material_list_id)
LEFT JOIN our_material_codes ON (our_material_codes.our_material_code_id = material_list.our_material_codes_id)
LEFT JOIN vendor_material_names ON (vendor_material_names.vendor_material_names_id = material_list.vendor_material_names_id)
LEFT JOIN material_uom ON (material_uom.material_uom_id = material_list.material_uom_id)
LEFT JOIN vendors ON (vendors.vendor_id = vendor_material_names.vendor_id)
LEFT JOIN customer ON (customer.cust_id = material_list.discount_id)

That above query takes longer than 5 minutes to run when there is about 1 million rows in the change_log table and in the invoice_materials table. I performed an EXPLAIN and tried to optimize the query with indexes, but it still didn't improve. Here is the EXPLAIN result: EXPLAIN STATEMENT

I understand that the design of the database is probably the problem, this is why I have all the fields that I have in material_list in the invoice_materials table because I am thinking of just storing that exact data into the invoice_materials table. Is this the best solution or is there a way to optimize the above query?

  • It looks like you are grabbing a lot of maximums of various columns according to some criteria If you are able to update to MySQL-8.0 CTEs and Window functions will simplify the required SQL here. Its looks like there are material tables in the subqueries that aren't used - look to see if these can be eliminated from the subqueries. Include SHOW CREATE TABLE change_log, looks like a index needs to include created_time. – danblack Jun 12 at 23:53
0

Instead of looking up the price in history, do the following...

When an invoice is created, grab the price that applies, and store it with the invoice.

That will eliminate most of the hassles and performance problems.

  • Yes that was the solution I came up with, that is why I said in the post I have the same columns in invoice_materials as in the material_list table to save the data and not have to pull it from the change_log table. I was just wondering if it were possible to optimize the query to reasonable speeds, since all the data is in the database. Thank you. – Genya Jun 14 at 12:35
  • @Genya - Finding the price for a particular day from a list of prices for date ranges is especially messy; it is unlikely to be optimizable. – Rick James Jun 18 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.