How to use subquery?

Question

I've performance problems with this query:

SELECT * 
          FROM post 
          JOIN post_plus 
          ON post_plus.news_id = post.id 
         WHERE category in(130,3) 
           AND approve=1 
           AND allow_main=1 
      ORDER BY kp_votes DESC, 
               kp_rating DESC LIMIT 30;

Optimizing join query

What I'd like to try is convert the above query to a "subquery" instead of using JOIN

What I've tried:

SELECT *
FROM post
WHERE category in(130,3)
    (SELECT *
     FROM post_plus
     WHERE post_plus.news_id = post.id)
  AND approve=1
  AND allow_main=1
ORDER BY fixed DESC,
         kp_votes DESC,
         kp_rating DESC LIMIT 10;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select kp_votes kp_rating from post_plus WHERE post_plus.news_id = ' at line 1

are all the columns in your where clause coming from post_plus? — Mark Giaconia, Feb 19 '14 at 22:22
Using a subquery most likely won't improve performance but on the contrary. Do all your columns have proper indexes on them that you join and/or filter by? — fejese, Feb 19 '14 at 22:23
Also, it would help if you'd prefix the columns by tables of rather their aliases so it's visible where they belong — fejese, Feb 19 '14 at 22:24
If you want us to help optimize a query, **you need to show us the table and index definitions**, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We also need row counts because that can affect query optimization greatly. If you know how to do an `EXPLAIN` or get an execution plan, put the results in the question as well. If you have no indexes, visit http://use-the-index-luke.com ASAP. — Andy Lester, Feb 19 '14 at 22:25
@fejese Yes I do... http://stackoverflow.com/questions/21779741/optimizing-join-query — Orlo, Feb 19 '14 at 22:26
@Orlo if it's about the same query - why do you create another question for the same thing? — poncha, Feb 19 '14 at 22:30
@poncha I ask `How to use subquery?` not `How to optimize my join query` — Orlo, Feb 19 '14 at 22:31
@Orlo I already started to write an answer and then figured it will be a very large one and will quote a lot of what manual has to say.. Maybe it's best if you first read the [**manual**](https://dev.mysql.com/doc/refman/5.1/en/subqueries.html) on the subject... It explains in detail several subquery use cases. — poncha, Feb 19 '14 at 22:41

score 1 · Answer 1 · answered Feb 19 '14 at 22:41

1

Fixing the syntax error would be this:

SELECT *
FROM post,
     (SELECT *
     FROM post_plus
     WHERE post_plus.news_id = post.id) tmp
WHERE category in(130,3)
  AND approve=1
  AND allow_main=1
ORDER BY fixed DESC,
         kp_votes DESC,
         kp_rating DESC LIMIT 10;

However I strongly believe that this is less optimal than the original join. It's rather a matter of indexing I guess. Could you show the indexes from the post table?

answered Feb 19 '14 at 22:41

fejese

4,481
4
27
36

it's not indexing! when you order calumns from second table it's always `Using temporary; Using filesort` here's a similar case http://stackoverflow.com/questions/3335177/mysql-performance-inner-join-how-to-avoid-using-temporary-and-filesort – Orlo Feb 19 '14 at 22:44
@Orlo in many cases, when mysql resorts to `filesort` - that is because of bad indexes. – poncha Feb 19 '14 at 22:48

score 0 · Answer 2 · edited May 23 '17 at 10:31

0

user Inner JOIN instead of JOIN to select only the matched rows. (A good Examples for join What is the difference between Left, Right, Outer and Inner Joins?). it bring you maybe performance.
use indexes (http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html) to accelerate your select.

edited May 23 '17 at 10:31

Community

1
1

answered Feb 19 '14 at 22:29

Marcel

383
1
8

2

Isn't `JOIN` the same as `INNER JOIN`? – Rudie Feb 19 '14 at 22:34

score -1 · Answer 3 · answered Feb 19 '14 at 22:52

-1

If optimization is not the problem here and you just want an alternative to JOIN then you could probably use WHERE EXISTS

In your case it could be:

SELECT *
FROM post
WHERE EXISTS
(SELECT *
 FROM post_plus
 WHERE post_plus.news_id = post.id)
AND category in(130,3)
AND approve=1
AND allow_main=1
ORDER BY fixed DESC,
     kp_votes DESC,
     kp_rating DESC LIMIT 10;

answered Feb 19 '14 at 22:52

Edper

8,869
1
25
46

I'm getting `ERROR 1054 (42S22): Unknown column 'kp_votes' in 'order clause'` – Orlo Feb 19 '14 at 22:57
Check the spelling of your fields in your table definition if it is really `kp_votes`. Or it could be that `kp_votes` belongs to `post_plus` and not `post` table? – Edper Feb 19 '14 at 23:06
kp_votes and kp_rating belong to `post_plus`. I tried changing to `post_plus.kp_votes` but I get the same `Unknown column 'post_plus.kp_votes' in 'order clause` I guess the subquery doesn't work. – Orlo Feb 19 '14 at 23:10
The `*` from `post` only has fields from `post` table and therefore `ORDER BY` from `post_plus` will have an error unless you use `JOIN` like in your original query then that would work. – Edper Feb 19 '14 at 23:23

How to use subquery?

3 Answers3