php request of mysql query timeout

Question

i'm trying to make a long mysql query and process and update the row founded:

$query = 'SELECT tvshows.id_show, tvshows.actors FROM tvshows where tvshows.actors is not NULL';
$result = mysql_query($query);

$total = mysql_num_rows($result);
echo $total;

while ($db_row = mysql_fetch_assoc($result)) 
{
//process row
}

but after 60 second give me a timeout request, i have try to insert these in my php code:

set_time_limit(400);

but it's the same, how i can do?

EDIT: only the query:

 $query = 'SELECT tvshows.id_show, tvshows.actors FROM tvshows where tvshows.actors is not NULL';

takes 2-3 second to perform, so i think the problem is when in php i iterate all the result to insert to row or update it, so i think the problem is in the php, how i can change the timeout?

EDIT:

here is the complete code, i don't think is a problem here in the code...

$query = 'SELECT tvshows.id_show, tvshows.actors FROM tvshows where tvshows.actors is not NULL';
$result = mysql_query($query);

$total = mysql_num_rows($result);
echo $total;

while ($db_row = mysql_fetch_assoc($result)) {
   //print $db_row['id_show']."-".$db_row['actors']."<BR>";
   $explode = explode("|", $db_row['actors']);

   foreach ($explode as $value) {
      if ($value != "") {

         $checkactor = mysql_query(sprintf("SELECT id_actor,name FROM actors WHERE name = '%s'",mysql_real_escape_string($value))) or die(mysql_error());

         if (mysql_num_rows($checkactor) != 0) {
            $actorrow = mysql_fetch_row($checkactor);
            $checkrole = mysql_query(sprintf("SELECT id_show,id_actor FROM actor_role WHERE id_show = %d AND id_actor = %d",$db_row['id_show'],$actorrow[0])) or die(mysql_error());

                if (mysql_num_rows($checkrole) == 0) {
               $insertactorrole = mysql_query(sprintf("INSERT INTO actor_role (id_show, id_actor) VALUES (%d, %d)",$db_row['id_show'],$actorrow[0])) or die(mysql_error());
            }

         } else {
            $insertactor = mysql_query(sprintf("INSERT INTO actors (name) VALUES ('%s')",mysql_real_escape_string($value))) or die(mysql_error());
            $insertactorrole = mysql_query(sprintf("INSERT INTO actor_role (id_show, id_actor, role) VALUES (%d, %d,'')",$db_row['id_show'],mysql_insert_id())) or die(mysql_error());

         }
      }
   }
}

How long does the query take if you execute it on the MySQL server, and how many records does it return? — rid, Mar 09 '13 at 13:52
i haven't tried it because it's not a simple query, i have to explode some string in php and insert it in the database, and i doesn't know if it's possible in the mysql server... — Piero, Mar 09 '13 at 13:53
Well, if the query takes a minute to execute, then you have a problem that needs to be addressed on the database server and you can't do much from PHP. If on the other hand the query is fast to execute, then the problem might be the processing that goes on in PHP. So it's important to first find out where exactly the problem is before you can debug it, otherwise you're just guessing. — rid, Mar 09 '13 at 13:55
[Please, don't use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) in new code. They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which. — TNK, Mar 09 '13 at 14:05
i understand, but if do that query on mysql it takes some second, so i think it's a php problem, because after the page give me the timeout request, i can see the the system continue to add the row in the table, so i repeat i think it's a php problem, what you think? — Piero, Mar 09 '13 at 14:07
@Piero, if it's a PHP problem, then you need to find which part of that `//process row` code is slowing things down. From your hints, it sounds like you're also doing some `INSERT`s in there, and if you're doing an `INSERT` for each row, then that's likely one of the problems. But unless you post exactly what you're doing, I'm not sure how much help you can get... — rid, Mar 09 '13 at 14:27
@rid i don't think it a problem in the code, i have insert all the code, so you can check.... — Piero, Mar 09 '13 at 14:30
@Piero, oh, wow, so you're doing potentially 2 `SELECT`s and 2 `INSERT` for *each* row? It might not be too much of a problem if you only have a few rows, but I have a feeling that you have more than a few. How many rows does the first `SELECT` return? — rid, Mar 09 '13 at 14:32
@Piero, then you're going to grow a long, white beard before 54000 `SELECT`s and 54000 `INSERT`s finish executing on a table that takes 3 seconds to return the results of a single `SELECT`... — rid, Mar 09 '13 at 14:34
ok, so what you suggest? how i can do it? it's not my fault if there are 27000 row :) — Piero, Mar 09 '13 at 14:36
@Piero, you definitely need to think of a better way to accomplish whatever you're trying to accomplish with that code. Try to see if you can make the algorithm more efficient. Try batch `INSERT`s. Try taking batches of input in separate scripts that run one after the other. Try using separate tables if you can. Try adding indexes for the column you're checking and removing unnecessary ones. Try caching whatever you can cache. Try doing as much as you can in the background and only select from a results table. — rid, Mar 09 '13 at 14:37
Either way, this is a problem that goes to the core of the application, and you're unlikely to solve it in any way other than looking again at how the application works and planning some significant refactoring, beginning with a second look at the database schema. — rid, Mar 09 '13 at 14:42

score 0 · Answer 1 · answered Mar 09 '13 at 14:04

Should definitely try what @rid suggested, and to execute the query on the server and see the results/duration to debug - if the query is not a simple one, construct it as you would in your PHP script, and only echo the SQL command, don't have to execute it, and just copy that in to the server MySQL command line or whichever tool you use.

If you have shell access, use the top command after running the above script again, and see if the MySQL demon server is spiking in resources to see if it really is the cause.

Can you also try a simpler query in place of the longer one? Like just a simple SELECT count(*) FROM tvshows and see if that also takes a long time to return a value?

Hope these suggestions help.

thank for the answer i have tried and the query on my sql takes 3 second...so is the processed of the row that takes time...so i think it's a php problem... — Piero, Mar 09 '13 at 14:08

score 0 · Answer 2 · edited May 23 '17 at 12:04

There are so many problems with your code.

Don't store multiple values in a single column. Your actors column is pipe-delimited text. This is a big no-no.
Use JOINs instead of additional queries. You can (or could, if the above weren't true) get all of this data in a single query.
All of your code can be done in a single query on the server. As I see it, it takes no input from the user and produces no output. It just updates a table. Why do this in PHP? Learn about INSERT...SELECT....

Here are some resources to get you started (from Googling, but hopefully they'll be good enough):

http://www.sitepoint.com/understanding-sql-joins-mysql-database/

http://dev.mysql.com/doc/refman/5.1/en/join.html

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

What is Normalisation (or Normalization)?

Let me know if you have any further questions.

thanks for the answer, i can make a call like explode() in mysql? — Piero, Mar 09 '13 at 14:56
@Piero: no, and you shouldn't need to. That's the point of normalization and not using multi-valued columns. If you have some arbitrary number of values, store them in a set of rows, not inside a single value in a column. — siride, Mar 09 '13 at 15:10

php request of mysql query timeout

2 Answers2