2. PHPRunner General questions
  3. SQL Nested Query
This topic is locked

SQL Nested Query

 		12/14/2023 12:37:54 PM
PHPRunner General questions
J
 JoeB7774 author

I have a query which gets time sheet data from tblTimeSheet. What I am trying to do is add an extra column to this query (TestValue) that looks at a second table (tblTestTable) and gets the value of TestVal for the maximum PPID that is less than or equal to the PPID value in tblTimeSheet. tblTestTable has the fields EmployeeID, PPID, TestVal. I've written the query below (which works) but it takes a really long time on 13,000 records (approximately 7 seconds of 'Loading...' on phpmyadmin). I'm wondering if there is a more efficient way to write this. In tblTimeSheet I have indexes for both PPID and EmployeeNumber. In tblTestTable, I have indexed EmployeeID and PPID. Any ideas?

SELECT DISTINCT
tblTimeSheet.EmployeeNumber,
tblTimeSheet.PPID,
tblTimeSheet.TimeSheetDate,
tblTimeSheet.TimeSheetID,
(SELECT tblTestTable.TestVal FROM tblTestTable WHERE tblTestTable.PPID <= tblTimeSheet.PPID ORDER BY tblTestTable.PPID DESC LIMIT 1) as TestValue
FROM
tblTimeSheet
LEFT OUTER JOIN tblTestTable ON tblTimeSheet.EmployeeNumber = tblTestTable.EmployeeID
fhumanes 12/15/2023

Hello,

All database managers have the functionality of showing the data access plan, when you facilitate an SQL.

Write an article to show this functionality in MySQL and using the free Mysql Workbench product: https://fhumanes.com/blog/guias-desarrollo/guia-12-phprunner-optimizar-accesos-a-mysql/

I have not analyzed the query that you have put, but the last line of "Left Join" makes no sense if you do not use the information in that table. In the "New Select" field, Join's information is not used.

Another huge cost is the "distinct", since you do the entire cost of the curricle of all records and then keep a small set of these.

That said with the "Explain Access" information you can analyze the costs.

Greetings,
fernando
Sergey Kornilov 12/15/2023

I see two problems here.

  1. DISTINCT

DISTINCT will always be slow unless you create a compund index on all fields included in this query. I don't think this is possible at all as you have field coming from another table. You need to find a way to remove DISTINCT from this query.

  1. SubQuery. Also going to be slow since database engine needs to execute a separate SQL query for each row in results. The correct approach is to use INNER JOINs.