sql combine two queries in one and SORT BY

Question

I have a large table and I've built two queries to first query the first 2 columns and then the other columns. My intention obviously to put first 2 columns data on top then append the other columns's data below. Here is the query.

SELECT * FROM (SELECT * FROM mytable WHERE A LIKE 'query%' OR G LIKE 'query%' ORDER BY LENGTH(A) )
UNION ALL
SELECT * FROM (SELECT * FROM mytable WHERE I LIKE 'query' OR J LIKE 'query' LIMIT 15)

This works okay but it is way too slow. Is there any way I can make it faster. Because I feel like I am making unnecessary SELECTS (4). Maybe I can make something like (I saw this suggestion here on Stackoverflow):

SELECT  * 
FROM 
        (
            SELECT *, 1 sortby FROM TABLE_A 
            UNION ALL 
            SELECT *, 2 sortby FROM TABLE_B
        ) dum
ORDER   BY sortby

But I have no idea how to do it for my case.

Thanks alot.

Why do you need the results from the first query on top of those from the second query? — Johnny Graber, Jan 04 '14 at 14:02
It is a technical dictionary and the words on the first columns are more important to show first. Words from the columns afterwards, are less important. — inrob, Jan 04 '14 at 14:04
Remove like if you need exact results,use= `WHERE I ='query' OR J ='query'` — Mihai, Jan 04 '14 at 14:04
The query works as I need it to, I'm looking to make it lighter if possible. — inrob, Jan 04 '14 at 14:06
Do you have any indexes on the A/G/I/J columns? Are they case-insensitive? — CL., Jan 04 '14 at 18:28

carleson · Answer 1 · 2014-01-04T14:53:33.383

1

Yes, you´re right. There is 2 un-needed selects in your query. This would work, but I doubt that it would be any better performance.

SELECT * FROM mytable WHERE A LIKE 'query%' OR G LIKE 'query%' 
UNION ALL
SELECT * FROM mytable WHERE I LIKE 'query' OR J LIKE 'query' LIMIT 15
ORDER BY LENGTH(A)

edited Jan 04 '14 at 14:53

answered Jan 04 '14 at 14:18

carleson

728
1
5
14

ORDER BY clause should come after UNION ALL not before – inrob Jan 04 '14 at 14:40
OK, Just add the "ORDER BY LENGTH(A)" to the last line (after the last select). – carleson Jan 04 '14 at 14:53

score -1 · Answer 2 · answered Jan 04 '14 at 14:25

-1

Have you try this :

Select m1.A,m2.I from (SELECT A FROM mytable m1 WHERE A LIKE 'query%' OR G LIKE 'query%'

ORDER BY LENGTH(A)

 UNION ALL

SELECT I FROM mytable m2 WHERE I LIKE 'query' OR J LIKE 'query' LIMIT 15))
ORDER BY m1.A,m2.I

answered Jan 04 '14 at 14:25

Nayeem Mansoori

821
1
15
41

sql combine two queries in one and SORT BY

2 Answers2