I've got a real strange problem using PDO for Mysql in php.
我在php中使用PDO for Mysql时遇到了一个非常奇怪的问题。
I have a table with about 30,000 rows in it.
我有一张桌子,里面有大约30,000行。
If I try and run this query:
如果我尝试运行此查询:
SELECT card_data.name, max(card_data.set_order) FROM card_data, theme_decks WHERE card_data.name = 'Mana Leak' and card_data.enabled = 1 GROUP BY card_data.name ORDER BY card_data.name
SELECT card_data.name,max(card_data.set_order)FROM card_data,theme_decks WHERE card_data.name ='Mana Leak'和card_data.enabled = 1 GROUP BY card_data.name ORDER BY card_data.name
(How its made)
(这个怎么做的)
SELECT card_data.name, max(card_data.set_order) FROM card_data, theme_decks
WHERE card_data.name = ? and
card_data.enabled = 1 GROUP BY card_data.name ORDER BY card_data.name ASC
$paramArray = array("Mana Leak");
$sth->execute($paramArray);
Through PDO it takes around 10 seconds execute!! Yet if i run the same query though phpmyadmin it takes less than a second..
通过PDO执行大约需要10秒!然而,如果我通过phpmyadmin运行相同的查询,它需要不到一秒钟..
The weird part is this query below will take less than a second though PDO:
奇怪的部分是下面这个查询将花费不到一秒虽然PDO:
SELECT card_data.name, max(card_data.set_order) FROM card_data, theme_decks WHERE
(card_data.name = 'Extraplanar Lens' OR card_data.name = 'Mana Leak' OR card_data.name =
'overgrowth') and card_data.enabled = 1 GROUP BY card_data.name ORDER BY card_data.name ASC
(How its made)
(这个怎么做的)
SELECT card_data.name, max(card_data.set_order) FROM card_data, theme_decks WHERE
(card_data.name = ? OR card_data.name = ? OR card_data.name =
?) and card_data.enabled = 1 GROUP BY card_data.name ORDER BY card_data.name ASC
$paramArray = array("Extraplanar Lens","Mana Leak","overgrowth");
$sth->execute($paramArray);
I can't for the life of me workout why searching for a single "name" would take 10 seconds when searching for multiple names only takes about 0.4 of a second...
我不能为我的生活锻炼为什么搜索单个“名称”需要10秒,当搜索多个名称只需要大约0.4秒...
EDIT: Plot thickens. I removed the parameterized statement and hardcoded the text to search
编辑:情节变浓。我删除了参数化语句并硬编码了要搜索的文本
SELECT card_data.name, max(card_data.set_order) FROM card_data, theme_decks WHERE
card_data.name = Mana Leak and card_data.enabled = 1 GROUP BY card_data.name ORDER BY
card_data.name ASC
Takes 0.000384092330933 seconds to query with PDO!!
使用PDO查询需要0.000384092330933秒!
BUT
但
SELECT card_data.name, max(card_data.set_order) FROM card_data, theme_decks WHERE
card_data.name = 'Mana Leak' and card_data.enabled = 1 GROUP BY card_data.name ORDER BY
card_data.name ASC
Takes 11.00254 seconds to to query with PDO!!
用PDO查询需要11.00254秒!
1 个解决方案
#1
3
Slow usually means you need indexes. I see you have a max() function on the card_data.set_order. This would mean your database has to go over every record to find the biggest.
慢通常意味着您需要索引。我看到你在card_data.set_order上有一个max()函数。这意味着您的数据库必须遍历每条记录才能找到最大的记录。
See if there is an Index on that field. If not, add one and see if that improves it
查看该字段是否有索引。如果没有,请添加一个并查看是否可以改善它