Join order by performance

Databases
1

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

Bye.

2

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

3

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

4

lol

np_sign
PRIMARY KEY (id),
KEY user_id (user_id),
KEY petition_id (petition_id)

np_user
PRIMARY KEY (id),
UNIQUE KEY email (email)

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

np_enum_zip
PRIMARY KEY (id),
KEY zip (zip)

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

5

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

6

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

7

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

8

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.

9

@BrandonK
yes is the PK.

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

10 

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 = u.id 
LEFT JOIN `np_user_detail` AS `ud` ON u.id = ud.user_id 
LEFT JOIN `np_enum_zip` AS `z` ON ud.zip_id = z.id 
WHERE (s.petition_id = 1) AND (s.status = 'confirmed') AND (u.status = 'confirmed') 
ORDER BY `s`.`id` DESC
11

General error: 2006 MySQL server has gone away
:frowning:

12

Thanks for the points :slight_smile:
Bye.

13

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

14

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

15

Why, are you having performance problems?