8 Things to Know About MySQL Programmer Starter

1. 1. Table MySQL is not an array
2. 2. Language SQL and MySQL Functions
3. 3. LIMIT, LIMIT, LIMIT
4. 4. Data Types
5. 5. Normalization and Modelling
6. 6. Index
7. 7. Concurrency, locking, and transactions
8. 8. Type Table
9. Cover

Time and again I am amazed at how bad the Successful software packages are out there. Apache is Easily outperformed by fnord, Is not even a real MySQL database, PHP Zend is so slow That actually made a business model out of selling performance enhancing hacks for it.-Diary Fefe
PHP + MySQL. Who does not know that this duo is a pair lovebirds popular in the open source world, even across the Internet. PHP has shifted Perl as a language of choice in creating Web applications, especially for beginners. And PHP is almost always side by side with MySQL; 90-95% + PHP scripts in www.hotscripts.com might read "requires PHP and MySQL." PHP books, including beginners, will not be considered afdol if not discussing MySQL. And programmers, slow or fast, willing or unwilling, it will be mengakrabi this one database software.
Unfortunately, many programmers have been playing around and using MySQL for a live performance at the production site, while still completely alien to the concept of relational databases. Even MySQL actually also has many deficiencies in terms of SQL features. So that programmers can not understand and exploit the full power + SQL relational database.
Create your beginner programmers who are still common in the MySQL database and the concept in general, need not feel inferior or afraid. In the IT world moves this fast-paced that there are too many things to learn. And most would learn as you go along, as he practiced. When he began to build a CMS for any satunet.com year 1999, I have only limited skills on SQL: SELECT only know, UPDATE, DELETE,CREATE TABLE, and DROP TABLE. What is JOIN, how to change the table schema, how to design a good database and normalized, everything is still blank. And all must learn as we go along. But still the end all was fine.
Through this article we will discuss what basic things you need to know in order to use MySQL with effectively and efficiently. Let's start with the case number one:
1. MySQL table is not an array
PHP or Perl programmer of course familiar with arrays and hashes, which are usually used to store a set of related data. Some of those who are not familiar with MySQL database tables will likely menganalogikan with array / hash (actually, an array of arrays or an array of two-dimensional). Table considered the same as an array, only once can be large and persistent (stored on disk).
This outlook is not entirely wrong, because after all in taking the records from the table is usually stored in the variable array / hash. Only, this perspective sometimes makes programmers do something like:
$ Res = mysql_query ("SELECT * FROM t1");
$ Rows = array ();
while ($ row = mysql_fetch_row ($ res)) $ rows [] = $ row;
echo "Number of records in the table t1 =", count ($ rows);
or create a table such as:
CREATE TABLE t2 (
F0 unsigned INT PRIMARY KEY AUTO_INCREMENT,
f1 int unsigned,
f2 VARCHAR (5),
F3 VARCHAR (200),
F4 VARCHAR (200),
F5 BLOB
);
What's wrong with the first PHP code above, which aims to find the number of records in a table? The programmers, who are too obsessed consider MySQL table as an array, try to build the first arraynya by filling out one by one element of the query results that later can use an array function count (). The problem is, what if the number of records there are 100 thousand? 1 million? 10 million? Not only that, finished in-count () the variable $ rows directly dumped again! In fact, there is a much more efficient way:
SELECT COUNT (*) FROM t1
Results querynya is a record only, no matter what the size of table t1.
Then what is wrong with both the SQL code? The programmers of Perl, in this case, obsessed with taking every record in the table with the function of DBI $ STH-> fetchrow_array ()
@ Row = $ STH-> fetchrow_array ();
print $ row [0]; # F0
print $ row [1]; # f1
print $ row [2]; # f2
# ...
Good is not it? Element 0 contains the value of the field F0, the first element of field f1, and so on. The problem was sacrificing ease of field names which is not very descriptive. Not to mention if the table needs to be changed by inserting the other fields between existing fields. Or the old fields need to be deleted. Everything will be a nightmare.
Some readers may be shaking his head. Is it true that there are PHP and Perl programmers who do both things at the top? Believe me, there. I never had to maintain ketiban sap cryptic table with field names like this.
2. Language SQL and MySQL Functions
SQL database MySQL is not it? Unfortunately, novice programmers sometimes limited his knowledge of SQL. In fact, to use the MySQL database effectively, he must not be lazy to learn a second language, namely SQL. If you do not learn SQL, then it is likely you will do things like:
$ Res = mysql_query ("SELECT * FROM Bigtable");
while ($ row = mysql_fetch_assoc ($ res)) (
if ($ row ['age']> = 40) (
echo "Found customers aged over 40 years! \ n";
break;
)
)
What's wrong with the code above? The PHP programmer trying to simulate a SQL WHERE clause with the testing conditions in the PHP code. Actually, that should be done is:
Bigtable SELECT * FROM WHERE age> = 40
This is a very save traffic client / server because not all the records must be sent from MySQL into your PHP program.
Some readers may be shaking his head. Is it true that there are programmers who like this? Believe me, there.
SQL already provides a way to sort data, format the display, grouping and filtering records, etc.. MySQL is also famous for providing many functions, ranging from the manipulation of dates, numbers, strings, etc.. Get to know SQL and MySQL functions; do this all in PHP Duplicate cause will be more efficient if done at the level of MySQL.
This is another example of PHP programmers who do not utilize the facility from MySQL:
$ Res = mysql_query ("SELECT * FROM customers");
while ($ row = mysql_fetch_assoc ($ res)) (
# Original format yyyy-mm-dd ...
preg_match ("/ (\ d \ d \ d \ d) - (\ d \ d?) - (\ d \ d ?)/", $ row [date], $ matches);
# ... and want to be dd / mm / yyyy
$ Date = "$ matches [3] / $ matches [2] / $ matches [1]";
echo "Name = $ row [name], date of birth
= $ date \ n";
)
Though MySQL has been providing formatting functions and the manipulation of dates:
$ Res = mysql_query ("SELECT name, DATE_FORMAT (date, '% d-% m-% Y') as date".
"FROM customers");
while ($ row = mysql_fetch_assoc ($ res)) (
# Do not need string manipulation fatigue more ...
echo "Name = $ row [name], date of birth = $ row [date]
\ n";
)
No points. 2 This may sound corny, but, like England's advice to say: know thy tools.
3. LIMIT, LIMIT, LIMIT
One reason why MySQL is very suitable for Web applications is to support the LIMIT clause. With this clause, it's easy to limit the number of records desired results in a single SQL command. No need to bother to play the cursor or by other means. Later, other databases such as PostgreSQL and Firebird were involved in giving support LIMIT functionality (with a syntax that is not exactly the same course).
Unfortunately, programmers are not familiar with MySQL itself well not use it properly.
$ Res = mysql_query ("SELECT name FROM users ORDER BY date");
$ Rows = array ();
for ($ i = 1; $ i <= 10; $ i + +) $ rows [] = mysql_fetch_row ($ res);
The programmer only intend to take 10 records, but told MySQL retrieve all existing records first. What if there are one hundred thousand records? 1 million records? 10 million? Should, every time you want just one, ten, fifteen records, you need to tell MySQL LIMIT clause passed.
SELECT name FROM users LIMIT 10 ORDER BY date
So that we can save the communication traffic of client / server and MySQL allow optimization of the query.
4. Data Type
Unlike PHP and skripting languages that allow us to put everything in a $ variable without the type declaration in advance, in MySQL we need to declare the data types of all fields that exist at the time of making a table. A programmer who does not know PHP MySQL sometimes tend to choose the wrong type of data (generally: choosing VARCHAR () when there are more appropriate data type) due to not knowing the types of data available.
Here are some examples of inappropriate selection of data types: a) selecting CHAR (8) or VARCHAR (10) instead of DATE to save the date; disadvantages, more extravagant places and could not take advantage of special functions date; 2) selecting CHAR (3) or CHAR (6) rather than TINYINT unsigned to store boolean data ("YES" and "NO", or "TRUE" and "FALSE"; much more efficient when expressed with 1 and 0 which only occupies one byte); 3) choose Float DECIMAL or DOUBLE instead to save money; disadvantages, Float and Double are often not based on binary and decimal fractions exactly in the store.
Number three often occur because programmers usually know only the single / double floating point numbers that are available in the programming language. Though generally databases provide a decimal-based floating rate that could save the exact decimal fractions.
MySQL manual is very helpful here; in the section on Column Types are described in detail the types of data, including a range of values that can be accommodated, how many bytes are occupied by these data types, etc..


5. Normalization and Modelling
Normalization, schema, entities, attributes, primary key (PK) and foreign key (FK), an entity table, table relationships, OLTP & OLAP ... all are terms which are common in the physical model database.Unfortunately, many novice programmers do not have the ability modeling. So if asked to design a database schema (a set of tables with field name and type) the result is not optimal even messy. Scheme of bad results in duplication of data, not scalable, poor performance, does not meet the requirements, etc..
Modeling certainly can not be taught in 1-2 days, especially in this short article. You need to read books on database modeling and learning from experience and from models that already exist. But some advice that perhaps I can give here is as follows.
One, the first step in modeling is to find an entity-entity. Entities are practically "object" that we will gelluti. For example, customers, products, and transactions. Each entity is generally placed in one table, the table is called the entity table. The second step is to look for attributes such entities. For example table greeting customers have attributes, name, address (street + city + zipcode + provincial + countries), the date record was added, and so on. The third step is to find relationships between entities-entities. Generally, the relation is one of the following: 1-1, 1-many, many-many. For example, the relationship between transactions and products is many-many, that means a purchase transaction may contain many products and of course a product can be purchased in more than one transaction. Every relationship will also be placed on the table, that table relationships.
Two, in terms of modeling there is no right or wrong model. That there is an appropriate model and is not appropriate for a particular purpose. For example, a simple model for simple applications. The more complex applications, even the more complicated model (number of entities, relationships, and attributes will increase). In general, as complexity increases, which had to be turned into an entity attribute, due to the fact 1-many/many-many relationship between attributes. For example, customers have the attribute address table. If we want to support many customers for a single address, the address will become their own entity and occupied tables. We then create a relation table-address customers.
6. Index
Index is something that is closely related to the implementation, rather than modeling. We often need to add an index on a field or many fields because if not added to the database performance does not become practical. All About indexing may also be too long to be explained in this short article, but essentially each column are: 1) have a sufficient range of values lot; 2) is located on a table containing many records; 3) is often mentioned in the WHERE clause and / or ORDER BY and / or GROUP BY; must be given to the index. This is because the index helps to find quickly a lot of value from existing values. Some examples:
* Each primary key index is generally automatically assigned by the database server, although the table still contains a few records or even empty. This is because the database should always check the existence of a value of this field whenever there is a record is added (remember, PK must not mean there are two records that contain the same value of this field). Without indexing, the checking will be linear and time-consuming.
* Field's birth date in the table customers are likely to be indexed. Even dayofyear (), this field may also need to be indexed. Why? Because: 1) the range of values large enough (365 days a year x + - 60 total years); 2) table size of potential customers; 3) is often mentioned in the WHERE clause (eg looking for customers who's birthday today).
* Field memo / notepad likely not need to be indexed (as usual). Why? Because even though a) the range of substantial value, and 2) tables can be big customers, but 3) this field is never mentioned in the WHEREclause directly (eg you never mentioned: ... WHERE pad = 'value of certain notes' or WHERE notes > 'some value'). [Note: there is another index of "unusual" in MySQL, which is FULLTEXT. But this is beyond the scope of our present article.]
* Field of sex may not need to be indexed, except if the ratio of male: female very drastic difference. Why? Cause: 1) the range of values there are only two: L (men) and P (female). Even if you give the index, will not improve performance.
7. Concurrency, locking, and transactions
Beginner web programmers sometimes do not realize that the program / script that made it is not like desktop programs run by one user. Rather, in one time there may be 10 or 100 users that "shoot" your script on the Web. Therefore, locking and concurrency issues is very important. An example is like this:
$ Res = mysql_query ("SELECT value FROM counters WHERE name = 'counter1'");
list ($ value) = mysql_fetch_row ($ res);
$ Value + +;
/ / Do something else first ...
$ Res = mysql_query ("UPDATE counter SET value = $ value WHERE name = 'counter1'");
Among the first line (when we take the value of record) and the fourth line (when we put the return value in the record) may have occurred several times on the record. For example, in the first row klien1 obtain the value $ value = 100. In the row-$ 3 value in increments to 101. But what happens if during the time interval that has become counter1 record value 103 (for example klien2, klien3, and klien4 have to incrementnya)? By the klien1, counter1 reset back to 101 and increments by klien2 consequently, klien3, and lost klien4 . Counter1 value should be 104.
For the above case, the solution is pretty easy. Perform an atomic increment:
/ / No need to take the first counter value ...
/ / Do something else first ...
$ Res = mysql_query ("UPDATE counter SET value = value +1 WHERE name = 'counter1'");
But in other cases, sometimes we have to do locking on tables or records to ensure that as long as we / / do something else ... klien2, klien3, and klien4 can not just raise the value of the counter:
mysql_query ("LOCK TABLES cuonters");
$ Res = mysql_query ("SELECT value FROM counters WHERE name = 'counter1'");
list ($ value) = mysql_fetch_row ($ res);
/ / Do something else first ... Increase value or something ...
$ Res = mysql_query ("UPDATE counter SET value = $ value WHERE name = 'counter1'");
mysql_query ("UNLOCK TABLES");
or (even better because we do not need melock whole table):
mysql_query ("SELECT GET_LOCK ('lock1')");
$ Res = mysql_query ("SELECT value FROM counters WHERE name = 'counter1'");
list ($ value) = mysql_fetch_row ($ res);
/ / Do something else first ... Increase value or something ...
$ Res = mysql_query ("UPDATE counter SET value = $ value WHERE name = 'counter1'");
mysql_query ("SELECT RELEASE_LOCK ('lock1')");
Remember, the side of locking may result in deadlock.
Transactions. Transaction also something that is used extensively in the database world, but almost never encountered in the programming language (this is because the data in a programming language is placed in the variable in the memory of all: there is no issue of free disk crashes / slow / damage / must be synchronized with data in memory). Therefore you need to understand the concept of books on the database.

8. Table Type
Known term in MySQL table handlers and types of tables. Currently there are three main types of tables that can be used in MySQL: MyISAM (default), BerkeleyDB and InnoDB. I need to know three things: 1) not all tables support transactions (MyISAM does not support transactions, so the COMMIT and ROLLBACK are not doing something properly if you apply on MyISAM table); 2) not all tables have the same performance characteristics (eg BerkeleyDB , slow if a large table size) and stored in the same way (for example, MyISAM tables are stored in three files:. MYI,. MYD,. FRM temporary tables InnoDB database and database-stored together in a disk area called tablespaces; 3) distribution of non-Max MySQL is not compiled with support for BerkeleyDB and InnoDB.
Number three is important to know because if we instruct MySQL to create a database with a particular type:
CREATE TABLE (...) TYPE = BDB;
And MySQL is not compiled to support BerkeleyDB, then MySQL will not protest the error, but made it to our table but with the default type is MyISAM. So you need to check first use the SHOW TABLESTATUS:
mysql> create table t4 (i int) type = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status from MyDB like 't4';
+------+--------+-...
| Name | Type | ...
+------+--------+-...
| T4 | MyISAM | ...
+------+--------+-...
Apparently MyISAM!
Cover
Actually to use MySQL with proper skills necessary skill-base, such as backup, restore, set up server parameters, monitor servers, etc.. But that was all the more a task of an administrator (DBA).