Top bad practices in MySQL [closed]

Question

There was another post allowing people to post their top bad practices in PHP, I had a gripe about MySQL practices and thought it'd be an interesting topic to hear the top bad practices in MySQL.

Up vote the ones you agree with

Answer 1

• While it can provide a performance benefit, the ability omit columns from the GROUP BY rates very high in my list of MySQL pet peeves. It's horrible for those new to SQL, or just not aware -- SO is littered with questions about why their query works in MySQL but won't port to Database X.

• use of backticks around non-keyword/reserved words. This suppresses good errors, telling people to not use reserved words.

• Improper use of brackets. Every WHERE clause does not need to be encapsulated in brackets, nor the ON portion of a JOIN clause.

• Lack of consistent formatting. The more readable, the less prone to errors and it's easier for others to understand.

• ANSI-92 NATURAL JOIN and the USING syntax. Just because they are ANSI, doesn't mean they were a good idea. Way better to be explicit about what criteria is being used - like SELECT *, if column names change or they get added/deleted, there can be very large implications if using the syntax. Even AskTom for Oracle recommends to forget that NATURAL JOIN even exists.

Answer 2

not putting sql keywords in CAPS

It kills the readability.

Answer 3

• selecting/sorting on unindexed columns. This should be avoided at all costs. Learn to use "explain" and understand it's output. If you're using ORDER BY in conjunction with LIMIT to get the "top N" then the sort needs to be on an indexed column.

• Wasting cycles on the DB server to do work that the web server(s) can do just as well, like sorting and grouping.

Sure it's easier to write

SELECT Name,Popularity FROM PizzaToppings ORDER BY Popularity;

but if your site goes viral and you need to scale out in a hurry, it's a LOT easier to throw a few more web servers in than it is to add more DB servers. And if you've kept the heavy lifting on the DB then you're still going to be compute bound on the hardest part to scale. Better to do:

SELECT Name,Popularity FROM PizzaToppings;

and then sort them in whatever language you're front ending the app with.

Answer 4

I can't stand it when people use commas instead of joins...

SELECT * FROM table1, table2 WHERE id1 = 4 AND id2 = id1_secondary

Lets be honest... this is more readable...

SELECT
    *
FROM table1
INNER JOIN table2 ON (
    id2 = id1_secondary
)
WHERE id1 = 4

Answer 5

Running on default settings.

So your MySQL runs slower and slower. I guess that's because MySQL is crap and you'd better move to Postgres/Oracle/MSSQL/MongoDB, right?

Wrong

How can it deal with your megabytes of data, if you hardly give it any RAM to load data in?

(ok.. maybe this is more serverfault related, by I still think it's something people should be more aware of - default settings are not enough!)

Answer 6

Writing "SELECT *" for your final output table. You can't know what order the columns coming out are going to be, so if you're pulling info out based on column index it's going to be wrong.

Answer 7

Taking advantage of the fact that MySql allows selecting columns no part of the GROUP BY clause. while it does work for many, it leads to bad resutls

Answer 8

Don't you think that some of mySQL bad practices would also be general to other engines, since SQL is pretty much a standard languages (even if the functionality changes from dbm to dbm).