CODEWITHSUNDEEP
mysqlsql
anytime
anytime

Reputation: 409

how to optimize sql join query

I want to optimize sql query 

SET SQL_BIG_SELECTS=1;
SELECT
    `Surveys`.`fname`
    , `Surveys`.`lname`
    , `Surveys`.`smobile`
    , `Surveys`.`semail`    
    , `Surveys`.`country`
    , `Surveys`.`city`    
    , `Surveys`.`sdob`
    , `Brand`.`brandname`
    , `Product`.`productname`        
    , `Surveys`.`outletcode`
    , `Surveys`.`outletname`
    , `Surveys`.`coupon_no`
    , `Users`.`username`
    , DATE_ADD(`Surveys`.datetime, INTERVAL 8 HOUR) as datetime
    , `Surveys`.`duration`    
    , userentry.couponcode as wcouponcode
    , userentry.couponcodecountry
    , userentry.prizename
    , DATE_ADD(userentry.datetime, INTERVAL 8 HOUR) as wdatetime    
FROM
    `Surveys`
    INNER JOIN `Brand` 
        ON (`Surveys`.`brandid` = `Brand`.`brandid`)
    INNER JOIN `Product` 
        ON (`Surveys`.`productid` = `Product`.`productid`) AND (`Surveys`.`brandid` = `Product`.`brandid`)    
    INNER JOIN `Users` 
        ON (`Surveys`.`userid` = `Users`.`userid`)
    INNER JOIN `userentry` 
        ON (`userentry`.`mobile` = `Surveys`.`smobile`)

here if am not writing SET SQL_BIG_SELECTS=1; it doesn't work

even with SQL_BIG_SELECTS its expire(sql timeout),

so how to optimize this query

Please help me

Upvotes: 1

Views: 4258

Answers (4)

kaushik0033
kaushik0033

Reputation: 677

There are always 2 things to consider when optimising queries:

  • What indexes can be used (you may need to create indexes)

  • How the query is written (you may need to change the query to allow the query optimiser to be able to find appropriate indexes, and to not re-read data redundantly)

The keys are:

1.You shouldn't need the subqueries - just do the direct joins and aggregate

2.You should be able to use INNER JOINs, which are typically more efficient than OUTER JOINs

Upvotes: 3

DRapp
DRapp

Reputation: 48139

From what the others noted about ensuring an index on the columns you are joining on, I only have one other suggestion. SOMETIMES, the MySQL query optimizer will try to use one of the other tables (such as product, brand, user) as the driving table based on lower counts from those tables or whatever other stats it may have available.

Since your query looks like all the other tables are more "lookup" reference only, and your Surveys table is the critical root table, just change

SELECT (... rest of query)

to

SELECT STRAIGHT_JOIN (... rest of query)

It tells the optimizer to do it in the order you've specified.

Upvotes: 0

Jhanvi
Jhanvi

Reputation: 5139

The common columns between both the tables that are included in join should be indexed.

Upvotes: 2

Cristian Bitoi
Cristian Bitoi

Reputation: 1557

You must index the columns that you use in your select statement (brandId, productid, userid, mobile)

Upvotes: 1

Related Questions