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.
Identifier
|
Maximum Length (characters)
|
Database Name
|
64
|
Table Name
|
64
|
Column Name
|
64
|
Index Name
|
64
|
Stored Function or Procedure
|
64
|
Trigger Name
|
64
|
View Name
|
64
|
Alias
|
255
|
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.
Index
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
Notes:
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).
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.
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.
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.
Comments
Post a Comment
Thanks for your valuable comments.