如何在PDO中编写此查询？ [关闭]

Question

I had used a query using mysql_query() and mysql_connect(), bun now i am using PDO in php

please help me, what is the best and fastest way for this query:

my old query was:

$sql = "SELECT * FROM products WHERE publish = '1' 
    AND id IN 
        (SELECT product_id FROM category_controller WHERE category_id IN 
                (SELECT id FROM categories WHERE publish = '1'))";
$result = mysql_query($sql);

my new query is:

$PDO = new PDO("mysql:host=localhost;dbname=mydb", 'root', '');
$sql = "SELECT * FROM products WHERE publish = '1' 
    AND id IN 
        (SELECT product_id FROM category_controller WHERE category_id IN 
                (SELECT id FROM categories WHERE publish = '1'))";

$result = $PDO->query($sql);

but the both ways that i had used was very slowly beacuse there are more than 5000 records in products table.

please help me to find a new and fastest way to run this query.

Answer 1

You may speed this up by using the JOIN concept rather than all the sub queries like this.

SELECT p.* 
FROM products p
   JOIN category_controller cc ON cc.category_id = c.id
   JOIN categories c ON c.publish = 1
WHERE p.publish = 1