Solved

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

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

Expert Comment

by:Dan Craciun
ID: 40320456
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
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 34

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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Init Waits 25 85
SQL inner join confusion 15 43
simple mysql statement 3 32
mysql joining from the same table 6 37
Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
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…
This video teaches users how to migrate an existing Wordpress website to a new domain.
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.

863 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

18 Experts available now in Live!

Get 1:1 Help Now