php pdostatement::execute taking FOREVER

Question

i'm designing a web application that has a rather complex query to search for points nearby the user's current location...along with quite a few other pieces of information. there are approximately 1.5 million rows in the main POI table, which is left joined to four other tables (the other smaller tables are for other data that is relevant to the main point of interest).

when running the query in the mysql console, it returns the data i'm looking for in 0.5 seconds or less (getting it to that point was enough of a PITA, but i finally figured out what indices i needed on the main POI table to make it happen)...but when i put the query into my app and have it run via the web (via jquery, ajax, and php pdo prepared statement), it takes upwards of 6-7 seconds to return any data...sometimes as long as 18-25 seconds.

what could i possibly be doing wrong that would cause the execute to take that long? is there something i need to make sure i'm doing that i might be forgetting somehow?

here's the relevant snippet of my php code (very simple) where the slowdown is actually happening:

$qry = "...";
$data = array(xxx); // user's lat/lon and other data we need

$sth = $this -> ci -> prepare($qry);
$sth -> execute($data);

for the record, running this as a non-prepared statement wasn't any better. in fact, it was actually a little bit slower...

please help me, i've been ripping my hair out over this all day. i thought once i finally got the query optimized it would solve the problem, but it turns out that's apparently not the case...

mysql> select
    t.treasureID, t.buriedByUserID, t.lockLevel, t.currentGoldValue, t.initialSilverValue,
    t.burySeen, t.initialGoldValue, t.prize1, t.buryPerkID, t.decoyOf,
    t.unlockAttempts, t.unlockedByKeypad, t.unlockedByUserID, t.prizeID,
    p.prizeDesc, p.validFrom, p.validUntil, p.sponsor, p.prizeName,
    userB.displayName as bDisplayName, s.sponsorID, s.sponsorName, pb.perkName,
    (DEGREES( ACOS( SIN( RADIANS( 40.6846 ) ) * SIN( RADIANS( t.latitude ) ) + COS( RADIANS( 40.6846 ) ) * COS( RADIANS( t.latitude ) ) * COS( RADIANS( -76.19613 - t.longitude ) ) ) ) * 60 * 1.1515 ) AS distance
    from treasures t
    left join prizes p on t.prizeID=p.prizeID
    left join userInfo userB on userB.userID=t.buriedByUserID
    left join sponsors s on p.sponsorID=s.sponsorID
    left join perksB pb on t.buryPerkID=pb.perkID
    where
        t.unlockedByUserID=-1 and
        t.buriedByUserID<>1011 and
        t.isGlobal=0 and
        t.latitude between 40.467351088803 and 40.901848911197 and
        t.longitude between -76.483560028513 and -75.908699971487 and
        ((1361820374 > p.validFrom and 1361820374 < p.validUntil) or p.validUntil is null)
        having distance < 15
        order by distance asc
        limit 0, 50;
+------------+----------------+-----------+------------------+--------------------+----------+------------------+--------+------------+---------+----------------+------------------+------------------+---------+---------------------------------------------------------------------------------------------------------------------------+------------+------------+---------------+-----------------+--------------+-----------+--------------+----------+---------------------+
| treasureID | buriedByUserID | lockLevel | currentGoldValue | initialSilverValue | burySeen | initialGoldValue | prize1 | buryPerkID | decoyOf | unlockAttempts | unlockedByKeypad | unlockedByUserID | prizeID | prizeDesc                                                                                                                 | validFrom  | validUntil | sponsor       | prizeName       | bDisplayName | sponsorID | sponsorName  | perkName | distance            |
+------------+----------------+-----------+------------------+--------------------+----------+------------------+--------+------------+---------+----------------+------------------+------------------+---------+---------------------------------------------------------------------------------------------------------------------------+------------+------------+---------------+-----------------+--------------+-----------+--------------+----------+---------------------+
+------------+----------------+-----------+------------------+--------------------+----------+------------------+--------+------------+---------+----------------+------------------+------------------+---------+---------------------------------------------------------------------------------------------------------------------------+------------+------------+---------------+-----------------+--------------+-----------+--------------+----------+---------------------+
50 rows in set (0.78 sec)

Can you show us: the query, it's timed execution from the MySQL client, the actual code that runs it, and the query log that demonstrates that PHP-run query is taking the time you think it's taking? — pilcrow, Feb 25 '13 at 22:10
i don't have an actual log...i am using the ChromePhp plugin and logged the time before the prepare, after the prepare, and after the execute. the prepare took 0 seconds, the execute varies (but usually longer than 6-7 seconds). — Matt Greco, Feb 25 '13 at 22:37
actually, i'd rather not post the query...at least not the entire thing. is there a convention for snipping out production names/variables? — Matt Greco, Feb 25 '13 at 23:52
You shouldn't need to worry about posting queries here if you're code is created well. Knowing DB info like field and tables names is useless if you don't have access to the database or a flaw in the code to exploit. Also, it's in the edit history anyway now... — BenOfTheNorth, Feb 26 '13 at 00:07
alright then. i'll put it back in... any ideas why the pdo execute would be so much slower than the mysql native query? — Matt Greco, Feb 26 '13 at 00:08
it looks like it might have something to do with index hinting. still working on it... — Matt Greco, Feb 26 '13 at 02:49
adding an index hint to use the correct index helped a bit (knocked the pdo query down to 5 seconds), but still ridiculously long when the query itself only takes a fraction of a second... — Matt Greco, Feb 26 '13 at 02:54
Are you using `bindParam()` / `bindValue()` and if so, are you setting the correct data types? — Phil, Feb 26 '13 at 03:01
i'm passing the values in as an associative array on the execute statement. i tried changing it to use bindParam but it didn't improve the speed at all (actually made it slightly slower)... — Matt Greco, Feb 26 '13 at 03:03
here's the link to the page i saw that references the index hint... http://stackoverflow.com/questions/12336457/php-takes-90x-longer-to-run-query-than-mysql-client — Matt Greco, Feb 26 '13 at 03:44
`for the record, running this as a non-prepared statement wasn't any better.` - so you did yourself a disservice, for some reason asking about PDO and prepared statement instead of real problem you face - performance and indexes. — Your Common Sense, Feb 26 '13 at 05:09
no, not really...when i run the query in mysql, it returns the data in less than a second. when i run it from php (regardless of whether it's a prepared statement or not), it takes anywhere from 3-6x as long (sometimes even longer). i don't see how that indicates the real problem involves my indexes...if that were the case, it would take a long time to run within mysql...no? — Matt Greco, Feb 26 '13 at 05:19

score 3 · Answer 1 · answered Feb 26 '13 at 05:36

In console, try to run it this way

SELECT SQL_NO_CACHE t.treasureID...

and this

EXPLAIN SELECT t.treasureID...

If it runs slow and explain shows many rows - the problem is the query itself.
If it runs fast even with no cache - try with console version of prepared statements
If the difference persists (console without prepared fast, console with prepared slow) - profile both queries and see what you can get from profiling results:

set profiling=1;
...run your queries
show profiles;
show profile for query 1;
show profile for query 2;

also try to play with ATTR_EMULATE_PREPARES but I have a feeling that it won't help much.
also make sure there is no other code on the PHP side that can take time

i'll give that a shot first thing tomorrow. my brain is fried right now after nearly 14 consecutive hours of trying to figure this out today...thanks for the answer! — Matt Greco, Feb 26 '13 at 06:07

php pdostatement::execute taking FOREVER

1 Answers1