Mysql查询执行需要大量时间

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 -

我正在开发一个时间表应用程序，并编写PHP代码来获取到日期为止的所有时间表。这是我为获取时间表而编写的查询

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

选择一个。accnt_name u。用户名、DATE_FORMAT(t。in_time,' % H:%我)亲密的,DATE_FORMAT(t。out_time’% H:%我)outTime DATE_FORMAT(t。work_time,‘% H:%我”)工作,w。wrktyp_name,t。言论,DATE_FORMAT(t。tmsht_date,' % d - b % - % Y ')tmshtDate王。loctn_name,s。serv_name,t。status_code,t。conv_kms convkms,t。conv_amount convamount从timesheets t、account a、services s、worktype w、work_location wl、a.accnt_code=t的用户。accnt_code w.wrktyp_code = t。wrktyp_code wl.loctn_code = t。loctn_code s.serv_code = t。serv_code和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.

where子句包含子句，用于从各自的表中获取各自代码的实际值。

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.

我在phmypadmin中运行了这个查询，在那里它没有任何问题。

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

需要帮助理解执行缓慢的原因是什么。

3 个解决方案

#1

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

使用EXPLAIN查看查询的执行计划。确保MySQL有合适的索引可用，并且正在使用这些索引。

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.

我们可以猜测它应该是u。usr_code，但这只是猜测。

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.

或者，考虑在WHERE子句中添加一些附加谓词，只返回所需的行，而不是表中的所有行。

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.

它是2015。现在可以抛弃旧式的连接操作的逗号语法，而使用join关键字，并将连接谓词从WHERE子句移动到ON子句。和格式。数据库并不关心，但它将使需要破译SQL语句的可怜人更容易理解。

  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.

如果相同的查询快速完成，并且来自不同客户端(相同的数据库，相同的用户)的整个resultset(大约720行)，那么问题很可能不是这个SQL语句。

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

我们不会期望SQL语句的执行会导致PHP“崩溃”。

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.

如果您正在存储整个resultset(例如，使用mysqli store_result)，则需要有足够的内存。但是，选择列表中的13个表达式看起来都比较短(格式化日期、名称和代码)，我们不会期望“备注”会超过几个KB。

For debugging this, as others have suggested, try adding a LIMIT clause on the query, e.g. LIMIT 1 and observe the behavior.

要调试它，正如其他人所建议的，尝试在查询中添加一个LIMIT子句，例如，LIMIT 1并观察行为。

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/

如何调试小程序http://ericlippert.com/2014/03/05/how-to-debug-small program /

#2

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

phpadmin会自动增加查询的限制，这就是为什么您会得到快速的结果。

Check how many rows are in table
检查表中有多少行。
Run your query with limit
限制地运行查询

#3

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

首先:修改您的查询，使其看起来像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.

在php代码。这将最大执行时间设置为无限制。因此，如果没有错误，脚本应该执行到最后。因此，您可以看到您的查询是否得到所需的结果。

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.

这将大大加快您的查询，因为它只获取前10个结果。稍后您可以将此值更改为更适合您的需要的值。除非您绝对需要完整的结果集，否则我建议您在查询中始终使用LIMIT。

#1