Is there any way to optimize this MYSQL query

Question

I have a MySql Table with the following schema.

table_products - "product_id", product_name, product_description, product_image_path, brand_id
table_product_varient - "product_id", "varient_id", product_mrp, product_sellprice, product_imageurl
table_varients - "varient_id", varient_name
table_product_categories - "product_id", "category_id"

and this is the Mysql select query i am using to fetch the data for the category user provided.

select * from table_products, table_product_varients, table_varients, table_product_categories where table_product_categories.category_id = '$cate_id' && table_product_categories.product_id = table_products.product_id && table_products.product_id = table_product_varients.product_id && table_varients.varient_id = table_product_varients.varient_id

The problem is that, as table contains lot of products, and each product contains lot of varients, it is taking too much time to fetch the data. And i doubt, as data will grow, the time will increase to fetch the items. Is there any optimized way to achieve the same.

Your help will be highly appreciated.

Devesh

Do you really need to fetch all items? Why don't just use "limit" in the query, and "paging" in the system? And do you really need to fetch all fields (you use * in the query) - you can specify which fields to get so that it fetches less fields (it will increase speed significantly) — Elena Sharovar, Oct 04 '13 at 08:02
@Christian Yes I am using &&. Is there any difference between && and AND. — Devesh Agrawal, Oct 04 '13 at 08:05
A well schema and [Mysql Indexes](http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html) will do — Drixson Oseña, Oct 04 '13 at 08:05
@ Elena.. Yes We can. If I am not bale to optimize it and reduce the time, that is the option left. — Devesh Agrawal, Oct 04 '13 at 08:06
What is required output , products or vairents or both? and which entity can be null — Digital Alchemist, Oct 04 '13 at 08:09
@Elena . Thanks for Suggesting specifying field, instead of *. Let me try this. Hope i can gain some performance. — Devesh Agrawal, Oct 04 '13 at 08:09

score 2 · Answer 1 · answered Oct 04 '13 at 08:08

2

You can use the EXPLAIN command to see whats happening in the server. Then you can optimize the request by creating indexes.

Some links:

answered Oct 04 '13 at 08:08

coding Bott

4,287
1
27
44

score 2 · Accepted Answer · answered Oct 04 '13 at 08:16

the query below would be a start, or something similar

SELECT
    * 
FROM
    table_products P
INNER JOIN
    table_product_categories PC 
ON
    PC.product_id = P.product_id
INNER JOIN
    table_product_varients PV
ON
    P.product_id = PV.product_id
INNER JOIN
    table_varients V
ON
    V.varient_id = PV.varient_id
where 
    table_product_categories.category_id = '$cate_id'

and as suggested do you really need to return * as this does mean selecting all columns from all tables within the query, which as we know from the joins themselves there a duplicates.

you should use indexing on tables for faster queries, set relationships between the joining tables this will also ensure referential integrity.

Hope this makes sense and helps :)

Thanks for the quick response. I can observe some significant difference. — Devesh Agrawal, Oct 04 '13 at 08:22

score 1 · Answer 3 · answered Oct 04 '13 at 08:07

1

yes you are correct, the query you are using above is not efficient:

you can get the same result as above by using ON clause instead of where clause.

the difference between them is, where clause gets all the rows and then filters out based on condidition specified.

Where as in case of ON Clause, the join haappens only on the rows which have met the condidtion specified in the ON clause

so..make your query as below:

So make use of joins instead of using where clause..

Hope this helps..

answered Oct 04 '13 at 08:07

Sai Avinash

4,683
17
58
96

As far as I know in MySQL they should both be optimised to execute with the same plan ( http://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where ). However putting the join conditions in the WHERE clause is less readable and prone to errors when someone forgets one of the join conditions resulting in a CROSS JOIN. – Kickstart Oct 04 '13 at 11:09

Is there any way to optimize this MYSQL query

3 Answers3