CODEWITHSUNDEEP
phpmysql
Rupesh Jain
Rupesh Jain

Reputation: 328

Mysql query takes a lot of time to execute

I am working on an timesheet application, and writing a PHP code to fetch all the timesheets till date. This is the query that I have written to fetch the timesheets -

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 the clauses to get the actual values of respective codes from respective tables.

The issue is that this query is taking a lot of time to execute and the application crashes at the end of few minutes.

I ran this query in the phpmyadmin, there it works without any issues.

Need help in understanding what might be the cause behind the slowness in the execution.

Upvotes: 3

Views: 1642

Answers (3)

spencer7593
spencer7593

Reputation: 108370

Use EXPLAIN to see the execution plan for the query. Make sure MySQL has suitable indexes available, and is using those indexes.

The query text seems to be missing the name of a column here...

  t.usr_code = u.    ORDER
                 ^^^

We can "guess" that's supposed to be u.usr_code, but that's just a guess.

How many rows are supposed to be returned? How large is the resultset?

Is your client attempting to "store" all of the rows in memory, and crashing because it runs out of memory?

If so, I recommend you avoid doing that, and fetch the rows as you need them.

Or, consider adding some additional predicates in the WHERE clause to return just the rows you need, rather than all the rows in the table.

It's 2015. Time to ditch the old-school comma syntax for join operation, and use JOIN keyword instead, and move join predicates from the WHERE clause to the ON clause. And format it. The database doesn't care, but it will make it easier on the poor soul that needs 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

Ordering on the formatted date column is very odd. Much more likely you want results returned in "date" order, not in the string order with month and day before the year. (Do you really want to sort on the day value first, before the year?)

FOLLOWUP

If this same exact query complete quickly, with the entire resultset (of approx 720 rows) from a different client (same database, same user), then the issue is likely something other than this SQL statement.

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

If you are storing the entire resultset (for example, using mysqli store_result), you need to have sufficient memory for that. But the thirteen expressions in the select list all look relatively short (formatted dates, names and codes), and we wouldn't expect "remarks" would be over a couple of KB.

For debugging this, as others have suggested, try adding a LIMIT clause on the query, e.g. 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 that the query is not the root cause of the behavior you are observing. I suspect The problem is somewhere else in the code.

How to debug small programs http://ericlippert.com/2014/03/05/how-to-debug-small-programs/

Upvotes: 2

Oak
Oak

Reputation: 362

First of all: modify you query so that it looks like the one given by Spencer

Do you get an error message when your application 'crashes' or does it just stop?

You could try:

ini_set('max_execution_time', 0);

in your php code. This sets the maximum execution time to unlimited. So if there are no errors, your script should execute to the end. So you can see if your query gets the desired results.

Also just as a test end your query with 

 LIMIT 10

This should greatly speed up your query as it will only take the first ten results. You can later change this value to one better suited for your needs. Unless you absolutely need the complete result set, I suggest you always use LIMIT in your queries.

Upvotes: 1

Droid
Droid

Reputation: 569

phpadmin automatically adds LIMIT to the query, that's why you got fast results.

  1. Check how many rows are in table
  2. Run your query with limit

Upvotes: 1

Related Questions