|
|
 |
|
|
 |
 |
|
|
|
Tutorial: MySQL
MySQL is the most popular SQL engine un the UNIX platform. It is both fast and efficient. Although it might not be as easy to use as other
competing products. But in our experience, that's just at first. After a while, you'll get used to it, and won't want to go back to anything less
efficient. :)
Both Perl and PHP can connect to MySQL. It is much better to manage data with these languages by using MySQL than writing
and reading data to/from text files.
MySQL is very extensive, but there are many tutorials about it on the Internet. There are a few listed at the end of this document.
However, we will give you a few tips that will help improve your experience with MySQL:
- First, you should download the MySQL manual to your computer. It's located in MySQL's homepage.
- Do table optimizations daily. Everytime you delete and add records on a table, you are defragmenting
the table. This will make it unnecessarily bigger, and inneficient. Use the OPTIMIZE TABLE command to do so.
- Never put all eggs in one basket. Don't try to put all data in one table, so that you can prevent redundancy. Also, remember
that tables have a file size limit of 2 gigabytes.
- Prevent loops in your programming as much as possible. If you want to insert a few thousand records into a table,
use LOAD DATA INFILE instead, or use the DELAYED option for INSERTs. Also, learn more about SQL. If you want
to count a number of records, selecting them all and counting them in a loop is not the right way. You can use COUNT(*)
for that. Also, learn thoroughly the GROUP BY option. It is quite a time saver.
- You must take care of backing up your database. The only way to do this is by using the utility mysqlhotcopy.
Your MySQL username and password is required to use it. Because of this, we can't make backups of your original, live
database. However, we can backup your backup, since it is a non-live copy of the database.
- Scripts that you write to use MySQL will usually contain your MySQL username and password. This is not safe as anyone
else on the server can see your code. So, give all your scripts permission 700 so that only you and the webserver can execute them.
In fact, chmod 700 your entire /cgi-bin/ directory. See our FAQ on Protecting your directories on our support section.
- Remember to keep documentation on the structure of your tables. And you should also download the table
backups to your computer for safe keeping.
- You should ALWAYS program your scripts around the fact that the MySQL database can fail. You can detect if your MySQL
database is not responding through the use of the connect command:
DBI -> connect (parameters) || oops();
If your script can't connect to the database, then the function oops() is executed. You can make that function anything you want.
Here are some links related to MySQL:
|
|
|
|
10 May 2004
