Trouble interpreting what is making my query so slow

174
January 09, 2019, at 06:00 AM

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
Rent Charter Buses Company
READ ALSO
Laravel Eloquent: How many to many relationship across multiple database schema

Laravel Eloquent: How many to many relationship across multiple database schema

I'm developing a system that is running with multiple databases (MariaDB) using the concept Multi-Tenancy, but I'm not using any framework for this

136
Codeigniter join return duplicate row

Codeigniter join return duplicate row

I'm trying to get records from a table using a join of other two tables, for doing this I did the following:

157
The adapter doesn't work in some functions

The adapter doesn't work in some functions

I did a recycleview job to display some data and I actually succeeded when I created it inside some functions such as the onCreateView method and the load method, and then dialog the function of displaying a window with buttons, and when I called the adapter...

292
Unable to load saved value in MultiselectList chosen

Unable to load saved value in MultiselectList chosen

Hi Im new on using ListBox MultiSelectList , My problem is I cant fetch the value onload of my page, Here is my Code

187