What is a recommended way to write MySQL queries neatly to improve readability?

Question

For a while I always had my queries as one line:

SELECT * FROM table WHERE col = value ORDER BY id DESC

Now that I've been told multiple times not to use *, I've been trying to find a way to make longer queries readable. So far I have something like this:

SELECT table.id,
       table.type,
       table.name

FROM table

WHERE table.id > $lastId
AND table.type = $type

ORDER BY table.id ASC

This seems okay, but are there any best practices around that I should have a look at?

My MySQL stuff is done within the context of PHP.

Answer 1

i have it this way:

SELECT 
    t.id,               //indent fields to pick out
    t.type,
    t.name
FROM 
    table t             //shorter alias (but alias wisely)
WHERE 
    t.id > $lastId      //indent conditions
    AND t.type = $type
ORDER BY 
    t.id ASC            //indent order parameters