MySQL ORDER BY breaks query

This is going to be a tough one to provide examples for... I have a MySQL 5.0 query similar to this:

SELECT a.field1, a.field2, b.field3 - bfield4 AS 'Balance', c.field5, c.field6, d.field7, e.field8 
FROM tablea a 
LEFT JOIN tableb b ON b.field1 = a.field4 
LEFT JOIN tablec c ON a.field6 = CONCAT(c.field1, c.field2) 
INNER JOIN tabled d ON a.field8 = d.field1 
INNER JOIN tablee e ON a.field6 = e.field3
INNER JOIN tablef ON f.field7 = a.field1 
WHERE 1 = 1 AND b.datefield IS NOT NULL

Open in new window


(It's going to be tough because I obviously cannot provide the data or actual tables/fields).  

(the 1 = 1 is because the SQL statement is being built through a menu and this ensures the WHERE isn't messed up in how it's coded).

All joins are joined to the primary table (a).

Now running the above query works fine.  Results returned in .21 seconds.

BUT, add a simple ORDER BY and it takes minutes (IF EVER - I'm still waiting) for the query to finish.

ORDER BY b.datefield

Open in new window


I tried creating an index on the b table, datefield but that didn't help.  Any ideas why trying to order would cause such a massive delay?

Much as I'd love to, upgrading the database is simply not an option.
LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
ORDER BY normally works on the result set and is the last thing that is done.  I don't see 'b.datefield' in your select list.  As far as I know, it needs to be there for ORDER BY to work properly.
0
_agx_Commented:
Are there any indexes involved?

Run an EXPLAIN on both queries, and see what's causing the difference.
https://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
@Dave Baldwin,

POTENTIALLY, b.datefield would not be in the SELECT portion of the statement.  But I'll test both ways.  If it must be there, I can enforce it appearing.

@_agx_,
I will look at this - but is EXPLAIN available in 5.0?  As I mentioned, that's the version we're using and upgrading at this time is not an option.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Ok, So I just checked, the b.datefield WAS in the select statement. And the ORDER BY did complete... 6900x longer than the none order by query (0.21 seconds vs. 1377.18 seconds).

For further reference, there are a total of about 161000 rows in the primary table (FROM).  

I ran Explain (cool - no idea this existed, thanks!)
here's the results - any tips greatly appreciated - should I create indexes?  do something that changes how this might run?  (Indexes are not my strong area).

Explain - with Order By
+----+-------------+-------+--------+-------------------------------------+-----------------+---------+------------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                       | key             | key_len | ref                          | rows   | Extra                                        |
+----+-------------+-------+--------+-------------------------------------+-----------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY,IntRefNmbr,InvoiceDateIndex | NULL            | NULL    | NULL                         | 160946 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | e     | ref    | UniqueVarChrFld                     | UniqueVarChrFld | 53      | adatabase.b.IntRefNmbr       |      1 | Using where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,UniqueIntFild               | PRIMARY         | 4       | adatabase.e.UniqueIntgrField |      1 | Using where                                  |
|  1 | SIMPLE      | cl    | ALL    | Code                                | NULL            | NULL    | NULL                         |      4 | Using where                                  |
|  1 | SIMPLE      | pl    | ALL    | Code                                | NULL            | NULL    | NULL                         |      6 | Using where                                  |
|  1 | SIMPLE      | h     | ALL    | NULL                                | NULL            | NULL    | NULL                         |   1574 |                                              |
+----+-------------+-------+--------+-------------------------------------+-----------------+---------+------------------------------+--------+----------------------------------------------+

Explain - without Order By
+----+-------------+-------+--------+-------------------------------------+-----------------+---------+------------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                       | key             | key_len | ref                          | rows   | Extra                                        |
+----+-------------+-------+--------+-------------------------------------+-----------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | b     | ALL    | PRIMARY,IntRefNmbr,InvoiceDateIndex | NULL            | NULL    | NULL                         | 160946 | Using where                                  |
|  1 | SIMPLE      | e     | ref    | UniqueVarChrFld                     | UniqueVarChrFld | 53      | adatabase.b.IntRefNmbr       |      1 | Using where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,UniqueIntFild               | PRIMARY         | 4       | adatabase.e.UniqueIntgrField |      1 | Using where                                  |
|  1 | SIMPLE      | cl    | ALL    | Code                                | NULL            | NULL    | NULL                         |      4 | Using where                                  |
|  1 | SIMPLE      | pl    | ALL    | Code                                | NULL            | NULL    | NULL                         |      6 | Using where                                  |
|  1 | SIMPLE      | h     | ALL    | NULL                                | NULL            | NULL    | NULL                         |   1574 |                                              |
+----+-------------+-------+--------+-------------------------------------+-----------------+---------+------------------------------+--------+----------------------------------------------+

Open in new window

0
Dave BaldwinFixer of ProblemsCommented:
I suggest that 'b.datefield' should be in the result set and should be indexed just to use ORDER BY.  Without an index, a full table scan must be done.  Although all the joins may negate that.  I use 'WHERE 1' in a lot of SELECTs for the same reason.

One oddity.  Your Explain charts do not show 'tablea' at all.  Is 'b' actually the primary table?
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
e is actually the primary table.  b,c,h,pl,cl are all joins.  Actually, b.datefield is InvoiceDateIndex.  I created this earlier (perhaps not right?)  As I said, indexes are not my strength... to the extent possible given my limitations on filed names, etc, can you suggest the index I should create (actual MySQL statement based on what I've provided?)
0
NerdsOfTechTechnology ScientistCommented:
How about making that huge join into a subquery then ordering by datefield (added column) like this:
SELECT s1.* FROM (
SELECT a.field1, a.field2, b.field3 - bfield4 AS 'Balance', c.field5, c.field6, d.field7, e.field8, b.datefield
FROM tablea a 
LEFT JOIN tableb b ON b.field1 = a.field4 
LEFT JOIN tablec c ON a.field6 = CONCAT(c.field1, c.field2) 
INNER JOIN tabled d ON a.field8 = d.field1 
INNER JOIN tablee e ON a.field6 = e.field3
INNER JOIN tablef ON f.field7 = a.field1 
WHERE 1 = 1 AND b.datefield IS NOT NULL
) s1
ORDER BY s1.datefield

Open in new window

0
Dave BaldwinFixer of ProblemsCommented:
Ok, you've confused me.  How about posting the SQL using the correct tables and fields as shown in the Explain above?  If InvoiceDateIndex is not in the primary table, I think that may go a long ways toward explaining why it is slow.
1
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Dave, I would if I could.  Client considers everything private.  I can't.  I know it's hurting the amount folks can help... and even if this can't be solved here, I've already learned some things I didn't know, so it was worth asking to me.

NerdsOfTech - interesting... hadn't thought of that... will give it a shot.
1
_agx_Commented:
Yes, I'd be curious if NerdsOfTech's query gets rid of the "Using temporary; Using filesort".  Do let us know the results.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Tried NerdsOfTech's idea and unfortunately, there was no change in execution time.

Last night, talking with others, the suggestion was to create a temporary table with the results SHORT of the order by, then query against that table and apply the order by there.  I don't like this idea because I don't like the idea of using temporary tables, however, I'm willing to give it a try.

Can I have some thoughts on using temporary tables?  I declared profession is not DBA so maybe I've got the wrong idea of temporary tables... is it a "good idea" to use them...? should they be avoided...?  Thoughts in general on the topic...

Thanks!
0
NerdsOfTechTechnology ScientistCommented:
Ok, let's solve the temp table issue by making the ORDER BY column from the FIRST TABLE in join:

 SELECT a.field1, a.field2, b.field3 - bfield4 AS 'Balance', c.field5, c.field6, d.field7, e.field8, b.datefield
 FROM tableb b 
 LEFT JOIN tablea a ON a.field1 = b.field4 
 LEFT JOIN tablec c ON a.field6 = CONCAT(c.field1, c.field2) 
 INNER JOIN tabled d ON a.field8 = d.field1
 INNER JOIN tablee e ON a.field6 = e.field3
 INNER JOIN tablef ON f.field7 = a.field1 
 WHERE 1 = 1 AND b.datefield IS NOT NULL 
 ORDER BY b.datefield

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
That's why I use a 'results' table in one instance.  Getting all the results using all of the parameters takes too long.  But the query on the 'results' table doesn't require any joins and is very fast compared to the original query.  Certainly worth a try.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Wow, talk about timing. I just worked with someone who gave me a suggestion that fixed it.

So the fix appears to be use the label.

I wish I could have provided more detail on the original query.  In any case, the original query had defined labels for all items in the SELECT portion.  When I changed the Order By to order by the label instead of the field, the query executed in .21 seconds instead of 21(+) minutes.
0
NerdsOfTechTechnology ScientistCommented:
To eliminate the discovered issue of "Using temporary; Using filesort" slowing down the query to a snail's pace, the sorting column b.datefield *MUST* be within the first table referenced in the SELECT/JOIN.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.