CODEWITHSUNDEEP
phpmysqlpdo
james
james

Reputation: 1041

PDO PHP Mysql slow on particular query

I've got a real strange problem using PDO for Mysql in php.

I have a table with about 30,000 rows in it.

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

(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..

The weird part is this query below will take less than a second though 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...

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!!

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!!

Upvotes: 1

Views: 985

Answers (1)

Bart Vangeneugden
Bart Vangeneugden

Reputation: 3446

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.

See if there is an Index on that field. If not, add one and see if that improves it

Upvotes: 3

Related Questions