Solved

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

Posted on 2014-09-12
10
38 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 34

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CA single sign on 2 87
No row return after calling the fillschema method 4 46
MySQL Error Code 2 19
two ways encryption with php 3 24
Dependencies in Software Design In software development, the idea of dependencies (http://en.wikipedia.org/wiki/Coupling_%28computer_programming%29) is an issue of some importance. This article seeks to explain what dependencies are and where they …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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…

813 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

16 Experts available now in Live!

Get 1:1 Help Now