php - 配置 Apache、PHP5 FPM 和 MySQL 进行长查询

Question

我有以下查询:

SELECT SQL_CALC_FOUND_ROWS
    a.`id_order`,`reference`,`total_paid_tax_incl`,
    `payment`,a.date_add as date_add, 
    a.id_currency, a.id_order AS id_pdf,
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,
    osl.`name` AS `osname`, os.`color`,
    IF((SELECT COUNT(so.id_order) FROM `ps_orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new,
    IF(om.`erp_order_id` is null, 0, 1) as erp_id
FROM `ps_orders` a
LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`)
LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1)
LEFT JOIN `ps_order_merge` om ON (om.`prst_order_id` = a.`id_order`) 
WHERE 1 
ORDER BY a.id_order DESC LIMIT 0,50;

加载大约需要 160 秒。该查询在一个非常特定的页面上调用(实际上仅从一个特定页面调用)。我们假设以下 URL 执行此类查询:

http://example.com/some/url/query.php

当我调用该查询时，浏览器显示

'500 Internal Server Error'

经过进一步调试，我从日志中得到以下内容:

Wed Mar 30 01:22:43.780576 2016] [fastcgi:error] [pid 12345:tid 1234567890] [client 123.459.78.123:12345] FastCGI: incomplete headers (0 bytes) received from server "/path/to/php5-fcgi", referer: http://example.com/some/file.php

FPM 慢速日志表明了这一点:

script_filename = /path/to/some/script.php
[0x00007f55bb18afd8] query() /path/to/some/script/DbPDO.php:80
[0x00007f55bb18aef8] _query() /path/to/some/script/Db.php:305
[0x00007f55bb18ad88] query() /path/to/some/script/Db.php:482
[0x00007f55bb18ab68] executeS() /path/to/some/script/AdminController.php:2198
[0x00007f55bb18a9e8] getList() /path/to/some/script/AdminController.php:1531
[0x00007f55bb18a8d0] renderList() /path/to/some/script/AdminController.php:1440
[0x00007f55bb18a788] initContent() /path/to/some/script/Controller.php:167
[0x00007f55bb18a5d0] run() /path/to/some/script/Dispatcher.php:349
[0x00007f55bb18a480] dispatch() /path/to/some/script/index.php:50

这是执行查询的确切文件和行:

[0x00007f55bb18ab68] executeS() /path/to/some/script/AdminController.php:2198

我想知道如何延长超时，以便 apache/php5-fpm 至少等待 160 秒。所以，至少现在；我可以加载那个有问题的页面。

我已经设置了我的 php.ini:

mysql.allow_persistent = On
mysql.connect_timeout = 160

编辑 1

我不被允许也无权修改查询。

Answer 1

我找到了解决我自己问题的方法，我最终注意到其中一个 LEFT JOINED 表没有正确索引。

我为此表创建了索引:

LEFT JOIN `ps_order_merge`

查询运行速度要快得多(不到一秒)。