Re: Limit query/function time

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Oct 22, 2009, at 3:30 PM, Phpster wrote:

Have you run an explain plan on the query?

Bastien

Sent from my iPod

Yes, I have. For giggles, I wanted to run it again. Here it is (slightly stripped down) and it looks good to me.

mysql> EXPLAIN SELECT
    -> `p`.`patient_id`,
    -> `p`.`address_id`,
    -> `p`.`patient_sub_id`,
-> AES_DECRYPT(`p`.`patient_first_name`, 'hidden_key') AS `patient_first_name`, -> AES_DECRYPT(`p`.`patient_last_name`, 'hidden_key') AS `patient_last_name`, -> AES_DECRYPT(`p`.`patient_middle_name`, 'hidden_key') AS `patient_middle_name`,
    -> AES_DECRYPT(`p`.`patient_dob`, 'hidden_key') AS `patient_dob`
    -> FROM `patient` `p`
-> INNER JOIN `center_patient` `cp` ON `p`.`patient_id` = `cp`.`patient_id` -> WHERE ((`p`.`patient_id` = '256783' OR `p`.`patient_sub_id` = '256783') AND `cp`.`center_id` = '109')
    -> ORDER BY `patient_id` DESC;
+----+---------+--------------------+------ +------------------------------------------------------------------+ | id | key_len | ref | rows | Extra | +----+---------+--------------------+------ +------------------------------------------------------------------+ | 1 | 4,4 | NULL | 2 | Using union (PRIMARY,patient_sub_id); Using where; Using filesort | | 1 | 8 | p.patient_id,const | 1 | Using index | +----+---------+--------------------+------ +------------------------------------------------------------------+
2 rows in set (0.00 sec)

There are about 350,000 records in `patient` and `center_patient` tables. The average number of rows returned from this query is less than 5.

~Philip


On Oct 22, 2009, at 4:14 PM, Philip Thompson <philthathril@xxxxxxxxx> wrote:

Hi all.

I'm running into a random issue where sometimes it take several minutes (up to 10 or 15) to complete a query. According to 1 or 2 references, this may be a mysql bug. These links explain the similar problem I'm experiencing:

http://forums.mysql.com/read.php?24,57257
http://forum.percona.com/s/m/790/

I did some testing yesterday and this 1 sql statement successfully queried (in milliseconds) approximately 50,000 times before it took several minutes to query, which hung all the other processes on the database. Because it occurs successfully so many times, I know the query is good.

Is it possible to set a time limit on a single function in PHP? E.g., when I call mysql_query() and it takes 30 seconds, then quit processing that function and continue on. I know I could set_time_limit() to a specific time, but that would cause a fatal error... and I want to be able to catch it.

Any thoughts on what direction I should go? Also, I'm going to do some research to see if a newer version of mysql has fixed this bug. We're running 5.0.45.

Thanks,
~Philip

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux