Problems with MYSQL query (efficiency)

Question

I'm having real problems with my Mysql statement, I need to join a few tables together, query them and order by the average of values from another table. This is what I have...

    SELECT
    ROUND(avg(re.rating), 1)AS avg_rating,
         s.staff_id, s.full_name, s.mobile, s.telephone, s.email, s.drive
    FROM staff s

    INNER JOIN staff_homes sh 
     ON s.staff_id=sh.staff_id
    INNER JOIN staff_positions sp 
     ON s.staff_id=sp.staff_id
    INNER JOIN reliability re 
     ON s.staff_id=re.staff_id
    INNER JOIN availability ua 
     ON s.staff_id=ua.staff_id 

    GROUP BY staff_id
    ORDER BY avg_rating DESC

Now I believe this to work although I am getting this error "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay".

I think this means that I have too many joins and because it is shared hosting it won't allow large queries to run I don't know.

What I would like to know is exactly what the error means (I have googled it but I don't understand the answers) and how I can work round it by maybe making my query more efficient?

Any help would be appreciated. Thanks

EDIT:

The reason I need the joins is so I can query the tables based on a search function like so...

     SELECT
 ROUND(avg(re.rating), 1)AS avg_rating

 ,  s.staff_id, s.full_name, s.mobile, s.telephone, s.email, s.drive
FROM staff s

 INNER JOIN staff_homes sh 
 ON s.staff_id=sh.staff_id
 INNER JOIN staff_positions sp 
 ON s.staff_id=sp.staff_id
 INNER JOIN reliability re 
 ON s.staff_id=re.staff_id
 INNER JOIN availability ua 
 ON s.staff_id=ua.staff_id

WHERE s.full_name LIKE '%'
AND s.drive = '1'
AND sh.home_id = '3'
AND sh.can_work = '1'
AND sp.position_id = '3'
AND sp.can_work = '1'


GROUP BY staff_id
ORDER BY avg_rating DESC

EDIT 2

This was the result of my explain. Also I'm not great with MYSQL how would I set up foreign keys?

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE ua ALL NULL NULL NULL NULL 14 Using temporary; Using filesort

1 SIMPLE re ALL NULL NULL NULL NULL 50 Using where; Using join buffer

1 SIMPLE sp ALL NULL NULL NULL NULL 84 Using where; Using join buffer

1 SIMPLE sh ALL NULL NULL NULL NULL 126 Using where; Using join buffer

1 SIMPLE s eq_ref PRIMARY PRIMARY 4 web106-prestwick.ua.staff_id 1

EDIT 3: Thanks lc, it was my foreign keys, they were not set up correctly. Problem sorted

Why all the joins? Your select statement only references the staff and reliability tables. — OTTA
– OTTA, Commented Jul 2, 2012 at 15:00
what exactly do you want in your sql? can you give us the table structure, maybe we can pare down the joins. — David Cheung
– David Cheung, Commented Jul 2, 2012 at 15:07
MAX_JOIN_SIZE is a MYSQL setting that limits the number of records your join statement can return. It's probably set by your shared hosting provider for a variety of reasons. dev.mysql.com/doc/refman/5.0/en/… — David Cheung
– David Cheung, Commented Jul 2, 2012 at 15:09
because depending on what the user searches for I may need to query the other tables. So the WHERE clause may be WHERE "sh.home_id = 2" the query shown is just the basic query without the where clause. Does this make sense? — Lee Mark Smith
– Lee Mark Smith, Commented Jul 2, 2012 at 15:09
If there are no foreign keys set up (e.g. staff_homes.staff_id referencing staff.staff_id), it would force mysql to do a row-by-row comparison and generate a large query. Or something in the search is doing the same. Could you post the results of an explain? — lc.
– lc., Commented Jul 2, 2012 at 15:20

Peter Kiss · Accepted Answer · 2012-07-02 16:09:28Z

1

Maybe you should use more and/or better indexes on the tables.

answered Jul 2, 2012 at 16:09

Peter Kiss

9,3292 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Elias Platek · Accepted Answer · 2012-07-02 15:34:20Z

0

According to the db you're using, the optimization may be faster or not with subqueries.

There may be 2 bottlenecks on your query:

try to remove the average function of your query. If your query speeds up, try to replace it with a subquery and see what happens.
The multiple joins often reduce performances, and there's nothing you can do except modifying your db schema. The simplest solution would be to have a table that precomputes data and reduces the work for your db engine. This table can be fulfilled with stored procedures triggered when you modify the data on the implied tables, or you can also modify the table values from your php application.

edited Jul 2, 2012 at 15:34

answered Jul 2, 2012 at 15:28

Elias Platek

1,1241 gold badge10 silver badges16 bronze badges

Collectives™ on Stack Overflow

Problems with MYSQL query (efficiency)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related