简短的php / mysql查询删除两个表选择的位置

Question

I need your help to speed up my mysql query

my php code view looks like this:

$product = query("SELECT `id`, `productname`, MAX(NO) FROM `product_list` WHERE 1");
$count1 = query("SELECT  `products` FROM `count1` WHERE 1");

list($noproduct) = mysql_fetch_array($count1);

if (mysql_num_rows($product) > $noproduct) {

    query("DELETE FROM `product_list` WHERE `NO` > ".$noproduct.");

}

and mysql base looks like this:

CREATE TABLE `count1` (
  `id` int(11) NOT NULL,
  `products` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `count1` (`id`, `products`) VALUES
(1, 9);

CREATE TABLE `product_list` (
  `id` int(11) NOT NULL,
  `productname` varchar(55) NOT NULL,
  `NO` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `product_list` (`id`, `productname`, `NO`) VALUES
(1, 'product1', 1),
(2, 'product2', 2),
(3, 'product3', 3),
(4, 'product4', 4),
(5, 'product5', 5),
(6, 'product6', 6),
(7, 'product7', 7),
(8, 'product8', 8),
(9, 'product9', 9),
(10, 'product10', 10),
(11, 'product11', 11);

ALTER TABLE `product_list`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `count1`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `count1`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


ALTER TABLE `product_list`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

My code executes three queries every time the page is reloaded and I need to make only one query.

I think that it should look like:

Delete from product_list where (select product_list)  > (select count1)

Answer 1

I think you are looking for this query:

DELETE FROM product_list 
WHERE NO > (SELECT  products FROM count1 LIMIT 1)

The addition of LIMIT 1 is just a safety measure, in case you would have more than one record in products.

Note that there is no real need to first select MAX(NO). It doesn't hurt to run the delete statement if there is nothing to delete. It will just do nothing then.