How to optimize SQL queries containing SELECT-related DELETE?

Question

$a = mysql_query("SELECT * FROM catalog_items where page_id='84'") or die(mysql_error());
while ($row = mysql_fetch_array($a)){
    $base_item = $row['item_ids'];
    mysql_query("DELETE FROM items_rooms where base_item = '".$base_item."'")  
or die(mysql_error());
        }

How can I run this script in PHP only with mysql querys? I’m having problems in php with TIMEOUT, so I thought I’d run mysql straight, I just don’t know how...

Answer 1

From what I understand you want to optimize the script and obviously the performance. It’s really not necessary to go all the way around.

See an example of DELETE with JOIN.

DELETE `items_rooms` FROM `items_rooms` AS T1 LEFT JOIN `catalog_items` AS T2
ON T2.item_ids = T1.base_item
WHERE T2.page_id='84'

I recommend that you back up before you run because I cannot guarantee the integrity of this by being unrelated to the structure of your system. But I believe it shouldn’t cause any trouble.

Nor do I guarantee that it will increase performance. It depends on the state of the structures, indexes, keys, etc.

There are other means of solving as by using multiple select. If this example above doesn’t change performance much, try to test other means, review how the data structure is, etc.

One remark, in older versions of Mysql, the use of the alias in DELETE expressions was not recognized. But I don’t remember at the time which version. Something close to 5.3 or 5.1.

Answer 2

What you can do is increase the PHP processing timeout. Before this processing add with the function set_time_limit($tempo) .

<?php

set_time_limit(0); //para um tempo infinito

set_time_limit($segundos); // o tempo que vc quizer em segundos

Answer 3

The following query will give you the same result (assuming the name of the fields in your table are consistent):

DELETE FROM items_rooms where base_item IN 
(SELECT base_item FROM catalog_items where page_id='84')

The query will delete from a list of base_item that was selected within the SELECT and you don’t even need PHP to run.