Solved

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

Posted on 2014-09-12
10
27 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
10 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 34

Expert Comment

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

HTH,
Dan
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sachin kaushik
Comment Utility
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 34

Expert Comment

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

Author Comment

by:sachin kaushik
Comment Utility
"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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
Thanks Guy!

I will try this solution.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
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…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
This video teaches users how to migrate an existing Wordpress website to a new domain.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now