Optimizing SQL, Better Performance than JOIN

Question

I have 3 tables with a total of about 200k rows.

My tables:

warehouse_stock

id  |   barcode      |  quantity  | warehouse_id
-------------------------------------------------
1   | 2211345661     | 21         | 1
2   | 2211345661     | 2          | 2
3   | 3452543234     | 1          | 2
4   | 3452543235     | 1          | 1
5   | 3452543236     | 1          | 1
6   | 3452543242     | 1          | 1
7   | 3452543245     | 1          | 1
8   | 3452543245     | 1          | 3
9   | 3452543245     | 1          | 1

product_variants

id    | ean(barcode)     | product_id
-------------------------------------
1     | 3452543245       | 14
2     | 3452543234       | 15
3     | 3452543245       | 16
4     | 3452543245       | 17
5     | 2211345661       | 18
6     | 2211345661       | 19

products

id     |   name     |   color  | manufacturer_id
------------------------------------------------
14     | Sample 1   | Red      | 1
15     | Sample 2   | Black    | 1
16     | Sample 3   | White    | 1
17     | Sample 4   | White    | 1
18     | Sample 5   | Orange   | 2
19     | Sample 6   | Pink     | 2

I just want to display SUM(quantity), product.name, barcode, manufacturer_id and warehouse_id. There is no relationship except barcode numbers.

Here is my code:

SELECT 
    SUM(ws.quantity) AS total_q,
    p.name,
    ws.barcode,
    p.manufacturer_id,
    ws.warehouse_id
FROM warehouse_stock AS ws
    INNER JOIN product_variants AS pv ON pv.ean = ws.barcode
    INNER JOIN products AS p ON p.id = pv.product_id
WHERE ws.warehouse_id = 1 AND p.manufacturer_id = 1
GROUP BY ws.barcode

It works, but mostly because that query goes off. Then we have to restart it.

How can change this to use sub-queries?

PS: There are no indexes on sql. And no permission for index. So that, I want your suggests. Subquery might be better then JOIN for such a query.

First problem and the easiest and best way to improve performance: "There are no indexes on sql." Second: What's with the repetition of `AND ws.stock_location_id = 1181`? Third: `JOIN`s are almost always faster than sub queries. I'm not sure if it is intentional, but if there are four variants for a product, the quantity will be counted four times. — G-Nugget, Dec 09 '13 at 17:30
There are more fields on tables, but i didnt write them above. — bencagri, Dec 09 '13 at 17:32
All tables http://www.sendspace.com/filegroup/vlBktwpCXIAf2SnWe%2B2XYaXmP7SjFWIb edit: @G-Nugget sorry ws.stock_location_id is copied multilpe, idk why...I fixed it... — bencagri, Dec 09 '13 at 17:40
Of course there is one row cuz of the data... Thanks @G-Nugget but, it would be perfect if I learn how to turn this into subquery. Cuz I have never used before. This topic helped me for logic : http://stackoverflow.com/questions/2577174/join-vs-sub-query?rq=1 — bencagri, Dec 09 '13 at 18:08
It is odd that the tables have no indexes at all, not even primary-keys. Relational DBMSes depend on indexes to perform reasonably. I doubt sub-queries will make a difference. You say there are 200K rows, but how many in each table? And, how many on your typical output once you filter by the typical WHERE clause? — Darius X., Dec 09 '13 at 21:21

score 0 · Accepted Answer · answered Dec 09 '13 at 18:13

0

Instead of rewriting the query to use sub-queries, you should be fine if you just add indexes on warehouse_stock.warehouse_id and product_variants.ean. Since the indexes will be used, the query will run much faster. If the query were to use sub-queries, it would be slower.

Also, since you're only GROUP BYing on ws.barcode, the rows contain information from multiple products, so p.name is only showing the first product name in the group. Each row does not represent a count of a specific product, but a count of a specific barcode which may cover multiple products, so p.name can be misleading in the rows.

answered Dec 09 '13 at 18:13

G-Nugget

8,666
1
24
31

The problem is, we are not allowed to add indexes. I totally agree with you. But there must be a way to do it? What do you seggest,except GROUP_CONCAT(p.name) ? Thanks. – bencagri Dec 09 '13 at 18:18
`GROUP_CONCAT(p.name)` would show you all of the product names, but if you need to have separate counts for each product and not each `barcode` you should `GROUP BY` the `product_id`. Without being able to add indexes, you won't be able to significantly improve the performance of the query; the tables need indexes. Making it use sub-queries will likely make it slower. – G-Nugget Dec 09 '13 at 18:22

Optimizing SQL, Better Performance than JOIN

1 Answers1