what is wrong in this mysql query syntax?

Question

I try to optimize my query because order by cause trouble,for more understanding, i try to achieve this good solution : Slow query when using ORDER BY

I have 70K in my table , when i go for this i have this eeror : #1248 - Every derived table must have its own alias

my original query looks like this :

SELECT * FROM 
 ( SELECT * FROM profile
    LEFT JOIN profileg USING( id )
    WHERE X NOT IN ( SELECT A FROM B ) AND ( S = 1 )
    DESC LIMIT 0, 20
) ORDER BY M

can you give some help please ?!? regards, jessica

General tip: If you say you get an error message, **TELL US WHAT IT IS**. As for your query, you do NOT need those extra brackets. Such type of bracketing is only required in sql server when joining multiple tables. In MySQL, they're not required, and you've put them in the wrong position anyways. — Marc B, Jun 28 '13 at 16:12
Is `profile` a table or a column? You select from it, and you also use it as a column in your WHERE and ORDER BY clause. If you have a column that has the same name as the table, you should change that or at least use a table alias so its more readable. — Nick Rolando, Jun 28 '13 at 16:21
@ Shredder , i replace the profile column by X for more clear — jess, Jun 28 '13 at 16:33

Nick Rolando · Accepted Answer · 2013-06-28T17:01:51.597

1

The error in your second query is that your subquery is not valid. Is this what you were after?

SELECT *
FROM ( SELECT * FROM profile
    LEFT JOIN profileg USING( id )
    WHERE X NOT IN ( SELECT A FROM B ) AND S = 1
    ) as table1
ORDER BY M DESC 
LIMIT 0, 20

Though I don't see the point of the subquery. Doing

SELECT * FROM profile
LEFT JOIN profileg USING( id )
WHERE X NOT IN ( SELECT A FROM B ) AND S = 1 
ORDER BY M DESC 
LIMIT 0, 20

seems to be essentially the same thing, except you're performing one less query.

edited Jun 28 '13 at 17:01

answered Jun 28 '13 at 16:51

Nick Rolando

25,879
13
79
119

but in real its not the same, i have 70K row and oreder by cause trouble , i want exactly this but it generate a new error #1248 - Every derived table must have its own alias : SELECT * FROM ( SELECT * FROM profile LEFT JOIN profileg USING( id ) WHERE X NOT IN ( SELECT A FROM B ) AND S = 1 ) ORDER BY M DESC LIMIT 0, 20 – jess Jun 28 '13 at 16:58
its not the same, i have 70K row and the order by cause trouble, – jess Jun 28 '13 at 16:59
In my answer, the first query runs faster than the second? They both use ORDER BY, as does both of your examples. – Nick Rolando Jun 28 '13 at 17:00
MANY THANKS Shredder, the alias solve my pb, and i win 3s on my query ! kiss regards, jessica – jess Jun 28 '13 at 17:10

what is wrong in this mysql query syntax?

1 Answers1