Databases
StackOverflow2013
Postsdbo
POMYSQL optimize & questions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMYSQL optimize & questions
    primarykey
    data
    text
    <p>I am trying to optimize my MySQL queries and I need some help. Here is my current query :</p> <pre><code>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 &lt;= DATE(NOW()) AND date_end &gt;= DATE(NOW()) </code></pre> <p>I am pretty sure it can be faster.</p> <p>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?</p> <p>Thank you for your time!</p> <p><strong>Solution</strong></p> <p>Here is my final query, if it can help anyone :</p> <pre><code>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 </code></pre> <p>Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeremy Stein
    1. USVallières
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMYSQL optimize & questions
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload