Solved

How can I increase the speed of the query which has multiple Where conditions and Order By.

Posted on 2014-09-12
10
56 Views
Last Modified: 2016-06-13
I have stored all the Permutations and combinations of all the attributes necessary to create a product based on color,weight and quality and stored it in attribute table.

In program I loop on the data from this table and create  Select sql to query another table which contains Product Price based on the attributes and I store it in another table.

I have suppose 7000 records in attribute table and 2 Lakh records in Price table.
So the program loops to 7000 records and Each select SQL queries 2Lakh records.

Select SQL contains Multiple Where Clauses and a Order by on Price to get the lowest price.

My question is how can I reduce the query execution time.
0
Comment
Question by:sachin kaushik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40320428
Please provide example data, e.g.

ProductNum       Name
1                       widget
2                       gadget


AttributeNum              ProductNum          Attribute
1                                         1                              white
2                                          1                              shiny

Also please provide an example of the query you're generating in that loop.

Thanks!
0
 

Author Comment

by:sachin kaushik
ID: 40320434
Attribute table

sno  color      Quality  Weight
1      blue       Good       3kg
2      red         Fair          1kg  
3      Yellow   Excellent   1.5Kg

Price table
sno     color     Quality    Weight(in kgs)    Market Price  Our Price
1          blue       Good       4                                   $400                $360
2          red        Excellent  2                                    $500                $450
0
 

Author Comment

by:sachin kaushik
ID: 40320451
Sorry I forgot to post in Price table  For Red I store - Crimson Red,Orange Red etc.

But Attribute I have Red bcoz Red is Prime color and in market u don't get perfect Red.
So I give Product close to Red and cheaper in that Red group.

select * from tbl_price
where color IN {Array [tbl data Entry i.e. RED ]    gives-> ( "Crimson Red", "Orange Red","Carrot Red" )}
AND Quality IN {Array [tbl data Entry i.e. Good ] gives-> ("Above Average","Medium","Not Bad" )}
AND Weight >= {tbl Entry of Weight}
Order by OurPrice ASC
LIMIT 1,1;
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40320456
Use EXPLAIN with your query and check for missing indexes, etc.

HTH,
Dan
0
 

Author Comment

by:sachin kaushik
ID: 40320458
Actually I did that and we did cramped our heads to solved this issue.

The problem is that this script is part of a cron which runs periodically and it
takes 60 minutes to finish

This script alone takes 45 minutes.

so u see it takes a lot time.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40320459
OK. Can you post the result of EXPLAIN SELECT?
0
 

Author Comment

by:sachin kaushik
ID: 40320463
"id"      "select_type"      "table"      "type"      "possible_keys"                  "key"              "key_len"      "ref"      "rows"      "Extra"
"1"      "SIMPLE"      "sp"        "index"      "color,weight,quality"      "our_price"      "8"                        -               "2"          "Using where"
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40321496
that query will need 1 single index covering the 3 fields used in the where clause

CREATE INDEX idx_search_price ON tbl_price ( Color, Quality, Weight );

optionally, you may add the ourprice as 4th field to the index;
still, the DB engine may want to decide to work without that index...
0
 

Author Comment

by:sachin kaushik
ID: 40321545
Thanks Guy!

I will try this solution.
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question