Clever Geek Handbook

Mysql query takes a long time to execute

I am working on a timetable application and am writing PHP code to get all timetables to date. This is the request I wrote for getting timetables -

SELECT a.accnt_name, u.username, DATE_FORMAT(t.in_time, '%H:%i') inTime, DATE_FORMAT(t.out_time, '%H:%i') outTime, DATE_FORMAT(t.work_time, '%H:%i') workTime, w.wrktyp_name, t.remarks, DATE_FORMAT(t.tmsht_date, '%d-%b-%Y') tmshtDate, wl.loctn_name, s.serv_name, t.status_code, t.conv_kms convkms, t.conv_amount convamount FROM timesheets t, accounts a, services s, worktypes w, work_location wl, users WHERE a.accnt_code=t.accnt_code and w.wrktyp_code=t.wrktyp_code and wl.loctn_code=t.loctn_code and s.serv_code=t.serv_code and t.usr_code = u. ORDER BY tmsht_date desc

The where clause contains clauses for getting the actual values ​​of the corresponding codes from the corresponding tables.

The problem is that this request takes a long time to complete and the application crashes at the end of a few minutes.

I ran this request in phpmyadmin, there it works without any problem.

Need help understanding what might be causing the slow execution.

+3


source to share


3 answers


Use EXPLAIN to view the query execution plan. Make sure MySQL has the correct indexes and uses those indexes.

Column name here is missing in query body ...

  t.usr_code = u.    ORDER
                 ^^^

We can "guess" what should be u.usr_code

, but this is just a guess.

How many rows should be returned? How big is the result set?

Is your client trying to "save" all the lines in memory and crashing as it runs out of memory?

If so, I recommend that you skip this and fetch the rows as needed.

Or consider adding some extra predicates to the WHERE clause to return only the rows you want, not all the rows in the table.

It 2015. Time to push the old school syntax for the join operation and instead use the keyword JOIN

and move join predicates from sentence WHERE

to sentence ON

. And format it. The database doesn't care, but it will make it easier for the poorest soul to decipher your SQL statement.

  SELECT a.accnt_name
       , u.username
       , DATE_FORMAT(t.in_time  ,'%H:%i') AS inTime
       , DATE_FORMAT(t.out_time ,'%H:%i') AS outTime
       , DATE_FORMAT(t.work_time,'%H:%i') AS workTime
       , w.wrktyp_name
       , t.remarks
       , DATE_FORMAT(t.tmsht_date, '%d-%b-%Y') AS tmshtDate
       , wl.loctn_name
       , s.serv_name
       , t.status_code
       , t.conv_kms      AS convkms
       , t.conv_amount   AS convamount 
    FROM timesheets t
    JOIN accounts a
      ON a.accnt_code = t.accnt_code
    JOIN services s
      ON s.serv_code = t.serv_code 
    JOIN worktypes w
      ON w.wrktyp_code = t.wrktyp_code
    JOIN work_location wl
      ON wl.loctn_code = t.loctn_code 
    JOIN users
      ON u.usr_code = t.usr_code
   ORDER BY t.tmsht_date DESC


Formatted date column ordering is very odd. It is much more likely that the results are returned in "date" order rather than in line order with month and day up to a year. (Do you really want to sort the day value first, before the year?)

Followup

If the same exact query is executed quickly, with the entire result set (approximately 720 rows) from another client (same database, same user), then the problem is likely something other than this SQL statement.

We did not expect the execution of the SQL statement to cause PHP to "crash".

If you are storing the entire result set (for example using mysqli store_result), you need to have sufficient memory to do this. But the thirteen expressions in the picklist look relatively short (formatted dates, names and codes), and we don't expect the "remarks" to exceed a couple of KB.

To debug this as others have suggested, try adding a LIMIT clause in your query, for example. LIMIT 1

and observe the behavior.

Alternatively, use a dummy query for testing; use a query that is guaranteed to return specific values ​​and a specific number of rows.

  SELECT 'morpheus'             AS accnt_name
       , 'trinity'              AS username
       , '01:23'                AS inTime
       , '04:56'                AS outTime
       , '00:45'                AS workTime
       , 'neo'                  AS wrktyp_name
       , 'yada yada yada'       AS remarks
       , '27-May-2015'          AS tmshtDate
       , 'zion'                 AS loctn_name
       , 'nebuchadnezzar'       AS serv_name
       , ''                     AS status_code
       , '123'                  AS convkms
       , '5678'                 AS convamount

I suspect the request is not the root cause of the behavior you are seeing. I suspect the problem is elsewhere in the code.

Debugging Small Programs http://ericlippert.com/2014/03/05/how-to-debug-small-programs/

+2


source


phpadmin automatically adds LIMIT to the query, so you get quick results.



  • Check how many rows are in the table
  • Run a Query with a Limit
+1


source


First of all: change your query so that it looks like Spencer shows

Do you get an error message when the application crashes or stops?

You may try:

ini_set('max_execution_time', 0);

in your php code. This sets the maximum execution time to unlimited. Therefore, if there are no errors, your script should run to the end. This way you can find out if your query got the desired results.

Also like the test end, your request with

 LIMIT 10

This should speed up your query significantly as it will only accept the first ten results. You can later change this value to one more suitable for your needs. If you don't need a complete result set, I suggest you always use LIMIT in your queries.

+1


source






More articles:


All Articles