Join order by performance

Can an ORDER BY (query with 4 tables) cause performance troubles ?
Is it better get rid of it or not ?
Thanks in advance.


I know but I’ve just made the db schema so also without
SHOW CREATE TABLE I know which field has an index.

yeah, but we don’t, so we can’t help you any further :slight_smile:


KEY user_id (user_id),
KEY petition_id (petition_id)

UNIQUE KEY email (email)

PRIMARY KEY (user_id),
KEY education_id (education_id),
KEY job_id (job_id),
KEY zip_id (zip_id),
KEY income_range_id (income_range_id),
KEY political_belief_id (political_belief_id),
KEY marital_status_id (marital_status_id)

KEY zip (zip)

Sorry but I can’t show you all the sql stuff
if not my top dog beat me up :frowning:

Does the query perform well without the order by clause? What indexes do you have on the tables (I’m assuming has a primary index on it)? Your order by isn’t complicated so it most likely isn’t the problem.

Do you have indexes on all join columns, and on the column?

whisher, if you do a SHOW CREATE TABLE for each table, we will be able to see and evaluate the indexes

with order by 63128 rows in 2:03.5940
without order by 63128 rows in 2:00.4552

All in all only a difference of 4 secs about.

yes is the PK.

@guido2004 is the PK but the other columns are without index (ie s.comment)

SELECT `s`.`id` AS `sid`, `s`.`registration_datetime` AS `sregdate`, `s`.`comment` AS `scomment`, CONCAT(u.first_name,' ',u.last_name) AS `fullname`, `z`.`zip`, CONCAT(z.municipality,'(',z.province,')') AS `livein` 
FROM `np_sign` AS `s` 
LEFT JOIN `np_user` AS `u` ON s.user_id = 
LEFT JOIN `np_user_detail` AS `ud` ON = ud.user_id 
LEFT JOIN `np_enum_zip` AS `z` ON ud.zip_id = 
WHERE (s.petition_id = 1) AND (s.status = 'confirmed') AND (u.status = 'confirmed') 
ORDER BY `s`.`id` DESC

General error: 2006 MySQL server has gone away

Thanks for the points :slight_smile:

your first join should really be an inner join, since you require users to be confirmed

if every np_user row must have a matching np_user_detail row, then they should all be inner joins

your indexes look okay, but of course the EXPLAIN will be the final clue as to whether the indexes are used properly

at this point i would recommend you throw away your ORDER BY clause because it doesn’t look like it’s important

How does the query run without the order by? Heck, how well does it run with? How slow are we talking? How many records are you dealing with, etc. etc. We need more info to help I think

Why, are you having performance problems?