Trouble interpreting what is making my query so slow

Question

I have a query that takes 15 seconds to get 350 results in a MySQL 5.6 Server and I am unable to diagnose why, I am still very new to database optimizing. U

The EXPLAIN visual does show some non-unique key lookups but each only says one 1 row look up.

The tabular EXPLAIN which I am not able to interpret and I am hoping someone else can here looks like .

I have tried switching the ending LIMIT = 350 to 100, 10, and the query takes exactly the same amount of time to run, about 15 seconds.

I have tried nixing the views but besides making it hard to recreate this query it did not improve performance.

Perhaps related, in other EXPLAIN statements in our MySQL DB, I've seen a view referenced with Materialized next to it, but that does not appear near next to any of the three views used in this query, in fact I don't even see the views referenced at all instead only the tables they reference. Is that a factor?

My last attempt was replacing the final selected column which is a listlineitems.* with the specific columns, since I've read that can improve speed and is just better practice, but I get the sense that is not going to dramatically improve this situation.

Here's the query -

SELECT 
0 AS 'Check',
DATE_FORMAT(`listlineitems`.`dateEntered`,
        '%Y-%m-%d') AS 'Date Entered',
`listlineitems`.`itemId` AS 'parentTableIdx',
`listlineitems`.`parentProjectId` AS 'parentProjectIdx',
`listlineitems`.`idx` AS 'ID',
IF(`listlineitems`.`active` = 1,
    'Active',
    'Inactive') AS 'Active/Inactive',
CONCAT(`listUsers`.`FirstName`,
        ' ',
        `listUsers`.`LastName`) AS 'Employee',
CASE `listlineitems`.`type`
    WHEN 1 THEN 'Time Entry'
    WHEN 2 THEN 'Expense Entry'
END AS 'Type',
`listcustomers`.`name` AS 'Customer',
`listlocations`.`name` AS 'Location',
`listareas`.`name` AS 'Area',
`listassets`.`name` AS 'Asset',
`listprojects`.`name` AS 'Project',
`listprojects`.`number` 'Project #',
`listprojects`.`autoassign` 'autoassign',
`listactivities`.`name` AS 'Activity',
(CASE `listlineitems`.`type`
    WHEN 1 THEN `listlineitems`.`qty`
    WHEN 2 THEN `listlineitems`.`qty`
END) AS 'Quantity',
`listlineitems`.`taxable` AS 'Taxable',
`listlineitems`.`totalAmount` - `listlineitems`.`taxAmount` AS 'Pre-Tax Amount',
`listlineitems`.`taxAmount` AS 'Tax Amount',
`listlineitems`.`totalAmount` AS 'Total Amount',
`listCustomers`.`idx` AS 'parentCustomerIdx',
`listLocations`.`idx` AS 'parentLocationIdx',
`listAreas`.`idx` AS 'parentAreaIdx',
`listAssets`.`idx` AS 'parentAssetIdx',
CONCAT(`listcustomers`.`name`,
        '/',
        `listlocations`.`name`,
        '/',
        `listareas`.`name`,
        '/',
        `listassets`.`name`,
        '/',
        `listprojects`.`name`) AS 'Path',
IF(`listlineitems`.`customerViewable` = 1,
    'Yes',
    'No') AS 'Cust. Viewable',
(CASE
    WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TotalCostToPSI` - `listexpenseentry`.`TaxCostToPSI`
    ELSE `listlineitems`.`totalAmount` - `listlineitems`.`taxAmount`
END) AS 'preTaxCostPSI',
(CASE
    WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TaxCostToPSI`
    ELSE `listlineitems`.`taxAmount`
END) AS 'taxCostPSI',
(CASE
    WHEN `listlineitems`.`type` = 2 THEN `listexpenseentry`.`TotalCostToPSI`
    ELSE `listlineitems`.`totalAmount`
END) AS 'totalCostPSI',
view_solinx2.lastAltered AS 'lastalteredSO',
view_polinx2.lastAlteredPO AS 'lastalteredPO',
view_invlinx2.lastAlteredInv AS 'lastalteredInv',
view_solinx2.lastAlteredAfterConfirmation AS 'lastAlteredAfterConfirmation',
view_solinx2.roleIdSO AS 'roleIdSO',
view_polinx2.roleIdPO AS 'roleIdPO',
view_polinx2.userIdPO AS 'userIdPO',
view_polinx2.lastAlteredafterConfirmation AS 'lastAlteredAfterConfirmationPO',
view_invlinx2.roleIdInv AS 'roleIdInv',
view_invlinx2.userIdInv AS 'userIdInv',
view_invlinx2.lastAlteredafterConfirmation AS 'lastAlteredAfterConfirmationInv',
view_solinx2.roleId AS 'roleId',
view_solinx2.userId AS 'userId',
view_solinx2.soId AS 'SOId',
view_solinx2.autoassignSO AS 'autoassignSO',
IF(view_solinx2.notNeeded = 1,
    'Not Needed',
    view_solinx2.number) AS 'SOname',
view_solinx2.dateEntered AS 'SoDate',
view_solinx2.totalSOAmount AS 'SoTotal',
view_invlinx2.invId AS 'InvId',
IF(view_solinx2.notNeeded = 1,
    '------',
    view_invlinx2.`number`) AS 'InvName',
view_invlinx2.dateEntered AS 'InvDate',
view_invlinx2.amount AS 'InvTotal',
view_polinx2.poId AS 'POId',
IF(view_solinx2.notNeeded = 1,
    '------',
    view_polinx2.`number`) AS 'POName',
view_polinx2.dateEntered AS 'PODate',
view_polinx2.amount AS 'POTotal',
(SELECT 
        listsalesorders.number
    FROM
        listsalesorders
    WHERE
        listsalesorders.idx = autoassign) AS 'test',
`listlineitems`.*
FROM
`listlineitems`
    LEFT JOIN
`listUsers` ON `listlineitems`.`individualId` = `listUsers`.`idx`
    LEFT JOIN
`listprojects` ON `listlineitems`.`parentProjectId` = `listprojects`.`idx`
    LEFT JOIN
`listassets` ON `listlineitems`.`parentAssetId` = `listassets`.`idx`
    LEFT JOIN
`listareas` ON `listlineitems`.`parentAreaId` = `listareas`.`idx`
    LEFT JOIN
`listlocations` ON `listlineitems`.`parentLocationId` = `listlocations`.`idx`
    LEFT JOIN
`listcustomers` ON `listlineitems`.`parentCustomerId` = `listcustomers`.`idx`
    LEFT JOIN
`listactivities` ON `listactivities`.`idx` = `listlineitems`.`activityCode`
    LEFT JOIN
`listexpenseentry` ON (`listexpenseentry`.`idx` = `listlineitems`.`itemId`
    AND `listlineitems`.`type` = 2)
    LEFT JOIN
view_solinx2 ON view_solinx2.idx = listlineitems.idx
    LEFT JOIN
view_polinx2 ON view_polinx2.idx = listlineitems.idx
    LEFT JOIN
view_invlinx2 ON view_invlinx2.idx = listlineitems.idx
GROUP BY `listlineitems`.`idx`
ORDER BY `listlineitems`.`dateEntered` DESC
LIMIT 10;

I am at a loss as to what else I can do to improve this and any suggestions are very much appreciated.

Answer 1

You are selecting everything from listlineitems table (100+ K records), joining many tables, then grouping by idx and then throwing out most results.

You can:

• Try to add unique index (dateEntered, idx) to listlineitems
• Try limit listlineitems by dateEntered if acceptable (WHERE dateEntered > DATE_SUB(NOW(), INTERVAL 30 DAYS)). dateEntered must be indexed
• Try to put select from listlineitems + grouping + limit into subquery so MySQL will do joins to only these 10 rows returned by subquery.
• Convert dependent subquery (listsalesorders) to left join