2

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 here 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 this.

I have tried switching the ending LIMIT = 350 to 100, 10 and 1 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;

Additional Info: There are indexes for customer, location, area, asset, project, dateEntered, itemId, lineitemtype and a few others that I don't think are used (they're columns are not used in the joins, should I IGNORE INDEX for them?).

Also, if it's useful to know, the customer to projects have their own connections. Projects have an asset foreign key, assets have an area foreign key, areas have a location foreign key, and locations have a customer foreign key, all many to one (one customer has many locations, one location has many areas etc).

Any suggestions or a plan of attack/what to look for is greatly appreciated. Any other questions on the structure of the database please just ask. I've been working on this for two days now and have got no where.

  • 1
    Different LIMITs changing nothing is due to the fact, all the hard work of collecting and sorting the data has to be done before the limit is applied. For a start, I'd drop the ORDER BY (only) and (then) remove the LEFT from all the JOINs. What's the effect? If you are retrieving all listlineitems columns anyway, it should not make too much of a difference, whether you list them individually or go for the wildcard. However, if you have some heavy column(s), which you do not really need - do not retrieve them. I'm missing the aggregate function(s) for which the GROUP BY is needed. – Abecee Jan 8 at 18:31
  • There is no aggregate for the GROUP BY and dropping it does improve the speed drastically to <1 seconds but it gives me different results for some reason. Which is even more confusing to me since the idx is a primary key, so its not like there is multiple IDX's to group. – bjk116 Jan 8 at 18:36
  • Well - with the GROUP BY included, you have every listlineitems.idx only once in your result set. For listlineitems.idx values, which had otherwise returned multiple rows, all other column values are kind of randomly selected from what has been found in the respective records for the individual columns. If you drop the GROUP BY, you could have some (or all) of the listlineitems.idx listed more than once - with the other columns reported exactly as saved in the table(s). You might want to identify the affected idx values and check, whether the random selection makes sense. – Abecee Jan 8 at 18:48
  • Ah ok good idea. I did just do a comparison of the two results, and only 8 rows idx's were not the same. I just checked and there are no duplicate occurences of idxs without the group by. So at this point then it was due to random selection? Makes sense and if so, thats completely fine for my purposes, this is onboarding table data, the user is meant to specify deeper which runs a more specific query. – bjk116 Jan 8 at 18:55
  • OK - if I read your SQL and your most recent comment correctly, the difference between the two result set is records arising from LEFT JOINs. And when you drop the GROUP BY you just relieve the server from hunting for (non-existent) duplicates. – Abecee Jan 8 at 19:09
0

Turning comments into an answer…

To start with: Different LIMITs changing hardly anything is due to the fact, all the hard work of collecting and sorting the data has to be done before the limit is applied.

In order to find the source of the resource consumption:

  • Drop the ORDER BY (only) and (then)
  • remove the LEFT from all the JOINs (one at a time).
  • Check the effect.
  • (As no aggregate function is used) Consider to remove the GROUP BY (and possibly use a WHERE instead).

Please comment, if and as this requires adjustment / further detail.

  • It was the GROUP BY that was the biggest culprit. The table that is being grouped on is by far our largest table at 110k rows, so running through that trying to group meaninglessly was a lot of wasted time. I was also confused as to why I got slightly dissimilar results and that was the biggest reason why I didn't just push the group by change immediately when I found it a few days ago, so thank you for clearing up that knowledge gap for me. I decreased the average query time from 16 seconds to less than 1, thank you! – bjk116 Jan 8 at 19:51
0

Ouch. What a nightmare!

Step 1: Find the 10 values of listlineitems.idx that are of interest. To assist this, have INDEX(dateEntered, idx).

Step 2: Make that the subquery to get the thing started

Step 3 (or later): Join back to listlineitems to get the other columns from it.

Step 4: Then Join to the rest. Please decide whether you need LEFT or not.

Something like:

SELECT ...
    FROM ( SELECT idx FROM listlineitems ORDER BY dateEntered DESC LIMIT 10 ) AS x
    JOIN  listlineitems USING(idx)
    LEFT JOIN ...  all the other stuff
    LEFT JOIN ...
    LEFT JOIN ...
    LEFT JOIN ...
    ORDER BY listlineitems.dateEntered DESC

Those changes should change it from 109K rows to look at to only 10. Note that the GROUP BY is not needed.

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.