how to optimize sql join query

Question

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

Did you see this? http://stackoverflow.com/questions/950465/mysql-sql-big-selects Are indexes on the join fields? How many records per table and how many records to you expect to be returned? Did you put an EXPLAIN in front of the SELECT to see how MySQL is going to execute this? — AgRizzo, Oct 04 '13 at 13:24
Thanks to all , but is there any query changes or just indexing is options? , I have upvoted all, thanks again — anytime, Oct 04 '13 at 13:39
The main question here is why do you need all those rows (to the extent that you need `SQL_BIG_SELECTS`)? Because if you are going to apply another couple of conditions or a LIMIT clause, you will have another query. — newtover, Oct 04 '13 at 13:41
For future code: You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a name that is a reserved word, or you have embedded spaces or punctuation in them, and those are terrible practices to avoid anyway. — Andy Lester, Oct 04 '13 at 13:42
Query looks great (assuming it returns the records you want). By using inner joins, you are going to get the best performance. Most comments and solutions are communicating the same thing - optimizing the tables with indices / indexes for read performance — AgRizzo, Oct 04 '13 at 13:46

score 3 · Answer 1 · edited Oct 07 '13 at 05:18

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

score 2 · Answer 2 · answered Oct 04 '13 at 13:25

2

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

answered Oct 04 '13 at 13:25

Jhanvi

5,069
8
32
41

score 1 · Answer 3 · answered Oct 04 '13 at 13:23

1

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

answered Oct 04 '13 at 13:23

Cristian Bitoi

1,557
1
10
14

score 0 · Answer 4 · answered Oct 04 '13 at 13:46

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.

how to optimize sql join query

4 Answers4