?
Solved

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

Posted on 2014-09-12
10
Medium Priority
?
61 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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 2000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

771 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