// January 25th, 2010 // No Comments » // Uncategorized
MySQL is one of the most popular relational database management systems (RDBMS) around, with over 6 million installations.
It’s a fantastic choice for any new developer, because of its open source nature, wide support and abundance of tutorials available on the subject.
Here are some great tips and tools (beside phpMyAdmin) to improve your MySQL coding, and to help you save time.
Installing SQL
If you’re a newbie to the server side coding arena, don’t worry. Installing SQL is very, very simple. Ok, well if you do it the geeky way it could take you hours, but there are several scripts out there that come packaged with an SQL installer. They also typically provide a server on your local machine which is very handy.
Mac only PHP, SQL & Apache Installer
Windows, Linux & Mac installer
A windows only LAMP installer
If you just want MySQL server, and like using command line then this might suit you.
For info on how to install and work with the MySQL server, check this NetTuts tutorial: Everything You Need to Get Started With MySQL. This article will introduce you to the world of relational databases allowing you to maximize your data’s potential.
SQL Tips
1- Optimize your code
Imagine if you have 100 records, 10 columns.Your working with a database of your clients details, and need to select their name, phone number and address. You’d write:
SELECT * FROM clients;
What’s up with that? Nothing. You’d then simply reference the columns you want later. Wrong!
That single line of code is highly inefficient. It’s fetching 1000 pieces of information from the database! However, if you wrote this:
SELECT name, phone, address FROM clients;
You’ve now cut down to only 300 calls. That’s a 700 saving. Probably not noticeable on a database that size, but you should always think about what your code is actually doing. That way, you can code efficiently in the first place, and make your app much more scalable.
Super Tip: In some cases, you can cut down to simply 3, by using a well thought out ‘WHERE’ clause.
2- Understand the code
Its been said that 60% of sql optimisation is understanding sql and the basics of databases. Spend time learning the difference between joins and sub selects. Its important that you know what technique to use and under what circumstance, so every hour spent here will yield noticeable results. So purchase a good book on MySQL and get learning. Knowledge is the root of power.
3- Keep Your Code Tidy and Organised
As with any coding you do, keep your SQL tidy and well formatted. Doing so will help you when debugging & generally managing code. There are several tools available to help you do so, complete with colour coding and code formatting.
Instant SQL Formatter
Instant SQL formatter is a free online service that beautifies and tidies your SQL code. It also converts SQL to html for easy posting to blog’s or web sites, and makes a great tool in any developers arsenal.
4- Add tables and columns sparingly
One of the most effective methods for keeping your application simple, performance consist ant and code streamlined is asking yourself, "Do I really need to add this table or column?" Could the task be accomplished by using a join or simply by storing data in one row and using coding later on to separate it out. By doing so you are cutting down and bloat, and making your app as dynamic and streamlined as possible.
5- Change one thing at once
Like SQL, racing cars are pretty technical. They require tuning and set-up in order to work to the absolute optimum. So does SQL. But, one thing that race car engineers never, ever do is change more than 1 thing at once. If you alter one table, one field, one column the effects multiplied over 1000’s could be disastrous. Even if it doesn’t break your whole database, and just has one little quirk, changing your code to do only one new thing at once will mean you know exactly what’s breaking your code.
6- Back-up Regularly
Whether your on a shared host, dedicated server or running on a local dev machine, backing-up is essential. On your local machine, make sure you back-up to an external hard drive (including your databases) or even better, to an online service such as Carbonite. Then, if you have a fire or flood in your office your backed-up, and all your development projects are stored away safely.
On shared / dedicated servers, you can set-up CRON jobs to back-up databases regularly, meaning if the site goes down, you can simply reload the database and carry on from where you left off, give or take a days worth of data.
Finally, when working on dev projects, before altering databases, its a great idea to back-up. That way, if your alteration breaks the whole database, you can simply replace the corrupt one with a back-up and try again. Combined with one alteration at once, and you have a solid SQL programming plat from to work from.
If your database is going to be read more than written to, consider using a cache. Doing so cuts down on calls to the server, and thus reduces load on the server.
The MySQL site has a great tutorial on using Query Cache, which essentially saves a SELECT command in the cache, then if its called again, the cache can serve the results rather than call the database again.
8- Use a cheatsheet / reference
Cheatsheet’s always seem to have a stigma attached to them. Probably because of the name, and their usage in exams. But, cheatsheet and references will help you no end in your general day to day working with SQL. Who wants to become an SQL walking encyclopedia? Remember all the stuff you really need, and the more obscure / less used functions are always only a few clicks away:
The MySQL official reference is essentially the SQL bible. Loads of great tips and advice, plus help in case you need a refresher on what an Identifier Qualifier is.
They also have past versions of SQL documented, plus SQL 5.0, 5.1 and 5.4.
9- Never stop learning
Just because you buy several books on MySQL doesn’t mean you’ll be Mr RDBSMS Rockstar straightaway. You need to practice what you’ve learned in real world situations, and always, always top up your knowledge by going back and re-learning what you’ve learned or checking out new tutorials on the matter. Here’s a few refreshers
MySQL GUI’s
A GUI (Graphic User Interface simplifies working with MySQL. Instead of working purely on the command line, you get a friendly interface that formats date, gives you a code editor and generally improves the whole experience of working with raw code. Think user friendly and time saving.
Here’s a few of the better ones on the market, both free and paid
RazorSQL is an SQL query tool, database browser, SQL editor, and database administration tool for Windows, Mac OS X, Linux, and Solaris. RazorSQL has been tested on over 29 databases, can connect to databases via either JDBC or ODBC. Its available for $59.95.
Viewing, searching, editing, importing, exporting, and structuring your data has never been so easy. With Querious, you can even open up raw CSV or Tab files in a spreadsheet-like document. Need to rename or reorder the columns? No problem. Searching for data is a breeze, and you can even convert the whole file to another format in a snap. It costs $25
A Database design and entity relationship diagram (ERD) tool for Mac OS X. SQLEditor replaces typing SQL by hand with dragging, dropping and clicking. It makes creating databases much faster and really reduces errors.
If you already have a database then SQLEditor can help you see what it looks like by creating a diagram of it. SQLEditor works on Mac, and supports Snow Leopard.
SQLGrinder”! is an SQL editor and developer tool that gives you the tools needed to make your database development easy. Using SQLGrinder you can create, edit and execute SQL statements. You can browse your database schema. SQL Grinder costs $59 and works on OSX.
Sequel Pro is a fast, easy-to-use Mac database management application for working with MySQL databases. Sequel Pro is free, open source and well worth a look at.
The MySQL GUI Tools Bundle for 5.0 includes the following products which are also supported for MySQL 5.1, and offered by the official MySQL site.
- MySQL Administrator 1.2
- MySQL Query Browser 1.2
- MySQL Migration Toolkit 1.1
Enjoy being a MySQL Rockstar, with heightened coding abilities! Why not share your favorite MySQL coding tip or GU editor with our readers. What’s the most useful hack you’ve learned?
