MYSQL optimize & questions

Hi, I am trying to optimize my MySQL queries and I need some help. Here is my current query :

SELECT *,
       (SELECT name FROM stores WHERE id = products.store_id) AS store_name,
       (SELECT username FROM stores WHERE id = products.store_id) AS store_username,
       (SELECT region_id FROM stores WHERE id = products.store_id) AS region_id,
       (SELECT city_id FROM stores WHERE id = products.store_id) AS city_id,
       (SELECT name FROM categories_sub WHERE id = products.subcategory_id) AS subcategory_name,
       (SELECT name FROM categories WHERE id = category_id) AS category_name
FROM products
WHERE date_start <= DATE(NOW()) AND date_end >= DATE(NOW())

I am pretty sure it can be faster.

I also have a problem, when I try to select all products by region_id (by adding something like "AND region_id = MYID") it doesn't work, and I wonder if it's possible to get it working, and how?

Thank you for your time!

Solution

Here is my final query, if it can help anyone :

SELECT p.*,
       s.name AS store_name,
       cs.name AS subcategory_name,
       cat.id AS category_id, cat.name AS category_name,
       c.id AS city_id, c.name AS city_name,
       r.id AS region_id, r.name AS region_name
  FROM products p
       JOIN stores s ON (s.id = p.store_id)
       JOIN categories_sub cs ON (cs.id = p.subcategory_id)
       JOIN categories cat ON (cat.id = cs.category_id)
       JOIN cities c ON (c.id = s.city_id)
       JOIN regions r ON (r.id = c.region_id)
  WHERE DATE(NOW()) BETWEEN p.date_start AND p.date_end

Thanks!

From stackoverflow

• SELECT A.*, B.name AS store_name, B.username as store_username, B.region_id as region_id, B.city_id AS city_id, C.name AS subcategory_name, D.name AS category_name FROM products A, stores B, categories_sub C, categories D
  WHERE B.id = A.store_id AND B.id = A.subcategory_id and C.id = A.category_id AND 
  A.date_start <= DATE(NOW()) AND A.date_end >= DATE(NOW())
  

  valli-R : Thanks! I understand now!

• You really need to learn to use JOIN. It's a fundamental operation for relational queries, just as a while() loop is a basic control structure in most application languages.

  Any book or tutorial on SQL should cover JOIN.

  SELECT p.*, 
   s.name AS store_name, s.username AS store_username, 
   s.region_id, s.city_id,
   cs.name AS subcategory_name,
   c.name AS category_name
  FROM products p
   JOIN stores s ON (s.id = p.store_id)
   JOIN categories c ON (c.id = p.category_id)
   JOIN categories_sub cs ON (cs.id = p.subcategory_id)
  WHERE DATE(NOW()) BETWEEN p.date_start AND p.date_end
  

  Now you can add more conditions to your WHERE clause:

    AND s.region_id = ?;
  

  ---

  One more comment... I was trying to be clever by using the BETWEEN predicate, but this might confuse MySQL's relatively simple optimizer. MySQL might have an easier time optimizing your original syntax:

  WHERE p.date_start <= DATE(NOW()) AND p.date_end >= DATE(NOW())
  

  valli-R : Okkkkkk I think I get it, I tried to read about JOIN, but I thought subqueries were easier, but is there a big difference between JOIN and Vijay's answer?

  cookiecaper : Vijay's answer is an implicit join. He hasn't specifically used the join keyword, but your database translates SELECTing from multiple targets like that into a join. Using a join like the one demonstrated here is simply more straightforward. Once you learn more about joins, you'll find them more convenient than subqueries, which are quite verbose, and much slower than joins.

  MarkR : BETWEEN is handled just fine by MySQL's optimiser. There are many optimisations it doesn't make, but BETWEEN should range scan an index if it can and thinks that's the right way to do it. BETWEEN should be equivalent to the above.

  Bill Karwin : @MarkR: Fair enough. It's worthwhile analyzing the optimization plan using EXPLAIN, in any case.