Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Feb 4 '11 at 21:10

This question came from our site for professional and enthusiast programmers.

closed as not constructive by Mark Trapp Nov 15 '11 at 20:11

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

3  
Belongs on programmers.stackexchange.com –  Joe Stefanelli Feb 4 '11 at 21:08
add comment

8 Answers

up vote 7 down vote accepted
  • 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.

share|improve this answer
2  
+1. nitpicking, don't you mean parentheses? :) –  Skurmedel Feb 4 '11 at 21:21
1  
@skurmedel: For you, I'd consult a thesaurus :p –  OMG Ponies Feb 4 '11 at 21:34
1  
@OMG Ponies: I reject your synonyms and substitute my own :D –  Skurmedel Feb 4 '11 at 21:38
    
@skurmedel: I'd substitute you with a small shell script :p –  OMG Ponies Feb 4 '11 at 21:42
    
@OMG Ponies: That wasn't nice hehe. Your shell script would need to be quite an AI feat too :) –  Skurmedel Feb 4 '11 at 21:44
show 14 more comments

not putting sql keywords in CAPS

It kills the readability.

share|improve this answer
3  
what difference does it make if they're in caps or not? –  Mahmoud Hossam Feb 4 '11 at 21:14
1  
@Phobia: It's easier to read if syntax is in CAPS, and everything else is not. Read any of my answers on SO for example. –  OMG Ponies Feb 4 '11 at 21:23
1  
personally,I don't have a problem with either –  Mahmoud Hossam Feb 4 '11 at 21:25
    
Caps looks kind of COBOL-ish but I nonetheless prefer this form of SQL. –  Alexandre Jasmin Feb 4 '11 at 21:38
    
I'd say that not using CAPS isn't so much a bad practice as it is a coding convention. If everyone is using small caps and everyone is fine with it, there's no harm. It's only a readability issue and doesn't affect performance. –  Andrew Arnold Feb 4 '11 at 21:42
show 1 more comment
  • 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.

share|improve this answer
1  
I can see for some operations loading the entire resultset in memory would kill your web servers. –  TheLQ Feb 4 '11 at 23:35
1  
@TheLQ : that's with selecting. With sorting and grouping you the whole resultset anyway, and these operations are very correctly better upscaled outside SQL than inside. A database should be used as a smart storage where you can easily get the desired information. It's not a sorting or grouping tool. –  Joris Meys Feb 5 '11 at 2:56
3  
If you have 10000 items and need top 10, the difference is between using highly optimized and indexed DB engine to select 10 records and send them over the network and sending 10000 items over the network and trying to stuff them into memory and sort them in place. I don't see how the latter is any better. –  StasM Feb 5 '11 at 5:23
1  
MySQL only allows one index per SELECT, and has a limited amount of space for indexes -- so I see a lot of difficulty satisfying your first bullet. –  OMG Ponies Feb 5 '11 at 8:42
    
@stasm good point, I updated the wording on the first bullet to cover that. As for the issue of pulling back 10000 records when you only needed 10... that kinda fails the "that the web server(s) can do just as well" test. ;) –  cabbey Feb 5 '11 at 19:01
show 1 more comment

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
share|improve this answer
8  
I can't stand it when people use unnecessary parentheses. :-) –  Joe Stefanelli Feb 4 '11 at 21:09
1  
The use of ANSI-89 syntax demonstrates larger issues (IE: lack of knowledge about OUTER joins). –  OMG Ponies Feb 4 '11 at 21:14
    
@Joe - They don't contribute to readability for you? @OMG - what are you talking about? –  Webnet Feb 4 '11 at 21:15
2  
@Webnet: Just the opposite for me, the parens in this case are distracting and make reading more difficult. –  Joe Stefanelli Feb 4 '11 at 21:20
    
Brackets imply order of operations, which is useless if there's only one criteria within. Formatting > brackets for readability. ANSI-89 syntax doesn't support OUTER joins, so use of ANSI-89 implies the user(s) don't know... –  OMG Ponies Feb 4 '11 at 21:25
show 1 more comment

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!)

share|improve this answer
    
+1 RAM is the big killer with MySQL, it does need space or it crawls. –  Orbling Feb 5 '11 at 19:18
add comment

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.

share|improve this answer
3  
MySQL is often used with languages that tend to pull associatively, so that ceases to be an issue. Personally I have never thought to rely on column position in output, I would not have assumed it was guaranteed. –  Orbling Feb 5 '11 at 19:17
1  
I used to, but I stopped doing it after the first time I had to use ALTER TABLE. –  dan04 Feb 5 '11 at 21:02
add comment

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

share|improve this answer
add comment

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).

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.