Saturday, 1 October 2011

MySQL Basics

What Is MySQL :

MySQL is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. It’s the most popular Open Source SQL database, provided by MySQL AB. MySQL AB is a commercial company that builds is business providing services around the MySQL database.

mysql vs mysqli       :        mysqli is the object-oriented version of mysql library functions.

Default Port             :        3306

Storage Engines      :        MyISAM, InnoDB, BDB(BerkeleyDB), MEMORY, CSV, BLACKHOLE  etc..

The following table describes the maximum length for each type of identifier.

Maximum Length (characters)
Database Name
Table Name
Column Name
Index Name
Stored Function or Procedure
Trigger Name
View Name

Advantages of InnoDB over MyISAM?

Row-level locking, transactions, foreign key constraints and crash recovery.

Advantages of MyISAM over InnoDB?

Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.


A data structure used for fast access to rows in a table. An index is usually built for the primary key of each table and can then be used to quickly find a particular row. Indexes are also defined and built for other attributes when those attributes are frequently used in queries


1) If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1,col2,col3), you have indexed search capabilities on (col1), (col1,col2), and (col1,col2,col3).

MySQL can't use a partial index if the columns don't form a leftmost prefix of the index. Suppose you have the SELECT statements shown below:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1,col2,col3), only the first query shown above uses the index. The second and third queries do involve indexed columns, but (col2) and (col2,col3) are not leftmost prefixes of (col1,col2,col3).

2) Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index length is 500 bytes (this may be changed when compiling MySQL). An index may use a prefix of a CHAR or VARCHAR field. Fixed-length and variable-length records.

Normalized database

A correctly designed database that is created from an ER model. There are different types or levels of normalization, and a third-normal form database is generally regarded as being an acceptably designed relational database.

Stored Procedures and Triggers

A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep reissuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.

A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even queries for which they are not needed.
Table Locking in MySQL?
You can find a discussion about different locking methods in the appendix.

All locking in MySQL is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.

1) The locking method MySQL uses for WRITE locks works as follows:

If there are no locks on the table, put a write lock on it. Otherwise, put the lock request in the write lock queue.

2) The locking method MySQL uses for READ locks works as follows:

If there are no write locks on the table, put a read lock on it. Otherwise, put the lock request in the read lock queue. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.

This means that if you have many updates on a table, SELECT statements will wait until there are no more updates.

No comments:

Post a Comment

Thanks for your valuable comments.

Gmail: Download blocked file

Hi, today we are going to learn, how to download blocked file in gmail. Sometimes you see following message in gmail: and you can...