In nowadays, web applications are mostly choose MySQL as database which is a central component of the widely-used LAMP web application software. Its popularity as well as the popularity of PHP. Most of the social networking website and frequently visited websites such as Facebook, Flickr, Friendster, Wikipedia, and Youtube are all using MySQL.
MySQL was developed by Micheal Widenius and David Axmark beginning in 1994. The first internal release of MySQL was on 23 May 1995. MySQL is named after the co-founder Monty Widenius's daughter My. The SQL words stands for Structured Query Language. The dolphin logo in MYSQL is called "Sakila" which was chosen from "Name the dolphin" contents. Ambrose Twebaze who is an Open Source software developer from Africa submitted this name and chosen to name the dolphin. My SQL source code that developed by MySQL development project team has made it available under a variety of proprietary agreements. My SQL was owned and sponsored by a single for-profit firm name Relational Software in the early and now owned by Oracle Corporation.
Overview of MySQL Database Management System
What is MySQL? MySQL is a Relational Database Management System (RDBMS) that widely use by most web application, web services and also some corporation. RDBNS means R-DB-MS. R stands for Relational, which relating information that store in two different table are able to retrieve or search when search the common elements to each of them. DB stands for the database, a repository for the information store. MS stands for management system which should allow user to retrieve, insert, update, delete or modify data. Relational DBMS has the advantage of efficient storage, and retrieval mechanisms for data, and uses normalization process during design of RDBMS
To stored, add, delete, retrieve and update data into computer database, we need a database management system such as MySQL server. Database management system is very important role in database. It handles the computing such as standalone utilities or as a part of other application and also increasingly embedded in third party software and other technologies. My SQL is a open source which mean it's free to use without any charges, under the General Public license. They are able associated with different OS platform such as Linux, Unix and windows. It is a multi-threaded using kernel threads and provide application program interfaces (APIs) for many programming language, including C, C++, JAVA, PHP and etc. MySQL is that it can be scaled down to support embedded database applications. Perhaps it is because of this reputation that many people believe that MySQL can only handle small to medium-sized systems. But the truth is that MySQL is the de-facto standard database for web sites that support huge volumes of both data and end users (like Friendster, Yahoo, Google).
There are 6 major benefits of MySQL:
Speed:
MySQL runs very fast. It supports clustered servers for demanding application but of course, the speed will still have to depend on the server side program's hardware.
Ease of use:
MySQL is a high-performance, relatively simple database system. MySQL already been configured, monitored and managed from the command line. It mean user doesn't need to required further knowledge to use it.
Cost:
Using MySQL is totally free because it is a open source database. MySQL is a part of LAMP. Most corporation using LAMP is because it is low cost, reliability and documentation.
Query Language Support:
MySQL knowledge are based on SQL because MySQL is developed from SQL. So it is easy to use compare with others database language.
Connectivity and security:
Data that store in the MySQL database are sharable to everyone because MySQL is fully networked and the database can be access from anyway on the internet.
Portability:
MySQL is platform independency. It can be run on Linux, Unix, windows and so on. It can be installed on Window XP, Window Server 2003, Red Hat Fedora Linux, Debian Linux and so on.
CHAPTER 2: MySQL PROGRAMS
2.1 MySQL server and server-startup program
There are many different programs in MySQL installation. Most MySQL distribution will include all of these programs except those programs with platform specific. Example: server-startup script cannot use on Windows. The exception is because the distribution is more specialized. Each MySQL program takes many different option. Most program will provide a help option that helping user to get description of the program. To invoke a MySQL program from the command line, enter the program name followed by any options or other arguments needed to instruct the program what you want it to do.
The main program that does most of the work in a MySQL installation as known as MySQL server. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files. When MySQL server starts, it listens for network connections from client programs and manages access to databases on behalf of those clients. MySQL Server has a set of system variables that affect its operation as it runs. System variables can be set at server startup, and many of them can be changed at runtime to effect dynamic server reconfiguration. MySQL Server also has a set of status variables that provide information about its operation. (MySQL, 2010)
2.2 MySQL installation-related programs
The following related programs are use on the Unix:
Compile MySQL Error Message File(comp_err) - comp_err creates errmsg.sys file that is used by mysqld to determine the different error code by display an error messages.
Package MySQL distribution as ZIP Archive (make_win_bin_dist) - the usage of make_win_bin_dist is to create an executable programs after building a MySQL distribution from sourceIt can be install in any location you wan because it pack the binaries and support files into a Zip Archieve
Create Source Distribution for Windows (make_win_src_distribution) - After configure and build the source distribution on a Unix or Unix-like system, this script is use to create a window source package to allow server binary to work with.
Generate Bug Report(mysqkbug) - The program can generate an error report and send it to Oracle. This is a shell script to run in Unix. Normally, we report bug is to visit oracle bugs database. This method is use if user doesn't have web access to access to the bugs database.
Upgrade MySQL System Table (Mysql_fix_privilege_tables) - Some versions of MySQL introduced the system table in MySQL database structure changes or add new privileges to support new features. When users upgrade to new versions of MySQL, users should update their system tables to ensure their structure is up to date. mysql_fix_privilege_table is an pervious script to upgrade the system table in MySQL database after an upgrade of mySQL.
Initialize MySQL Data Directory(mysql_install_db) - mysql_install_db initializes the MySQL data directory and create a system table which it contains, if they do not exist.
Improve mySQL installation security (mysql_secure_installation) - this program allow user to improve mySQL installation security in several
ways:
a. set password for root account
b. local host from out can remove root account
c. remove anonymous account
(MySQL, 2010)
2.3 MySQL Client Programs
The following client programs are only use for Unix:
The MySQL Command-Line Tool - It has 2 usage, interactive and non-interactive. When use in interactively, query results are show in an ASCII table format. When use in non-interactive, results are listed in tab-delimited format. Use command option can change the output format.
Client for Administering a MySQL Server (mysqladmin) - mysqladmin is used to perform administrative operations by a client. Client can use it to check the server's configuration and current status, create and delete databases.
A Table Maintenance Program (mysqlcheck) - the mysqlcheck is allow client perform table maintenance. Client able to checks, repairs, optimizes or analyzes tables.
Database backup program (mysqldump) - mysqldump client backup program written by logr Romanenko. It can be used to dump the database or database backup or transfer to another SQL server set (doesn't must be a MySQL server). Dump typically contains SQL statements to create tables, populate it or both.
Display database, table and column information (mysqlshow) - the mysqlshow allow client can immediately see which database exist, the tables and a table's indexes or column. (MySQL, 2010)
2.4 MySQL Administrative and Utility Programs
The following client programs are only use for Unix:
Offline InnoDB File Check Sum Utility (innochecksum) - innochecksum use for verify the InnoDB printed documents. This tool reads a InnoDB table file's space, calculate the checksum of each page and compare the checksum calculation that is stored but does not match the inspection and report including a description of the damaged page. It was originally off the table space to speed up the file after verifying the integrity but also can be used in the file after copying. Because the checksum does not match InnoDB causes deliberately shut down a running server, it is better to use this tool rather than wait for a server to produce usage to encounter the spoilt pages.
Display Full-Text Index information (myisam_ftdump) - myisam_ftdump displays MyISAM table information in FULL TEXT indexes. It reads the MyISAM index file directly, so it must be located on the server running on the host table.
MyISAM Table-Maintenance Utility (myisamchk)- there are myisamchk General Option, myisamchk Check Option, myisamchk Repair Options, Other myisamchk Options and myisamchk Memory Check. The myisamchk utility can obtains information about database tables or checks, repairs, or optimizes the information myisamchk works with MyISAM tables (tables that have .MYD and .MYI files).
Generate Compressed, Read-only MyISAM Tables (myisampack) - this utility compresses MYISAM tables. MYISAMPACK operate by separately compress each column in the table. The compression of the data can be up to 40% until 70%.
During after using the table, server will only decompress the columns that need to be use by read into memory. It is better in performance when access the rows because only the exactly row needed to be decompress.
Client for Checking Access Privileges (mysqlaccess) - mysqlaccess is a diagnostic tool that distributed by Yves Carlier. It check the name of host, name of user and combination access of database. Please remember that, mysqlaccess check only access by the user, database and host tables. It doesn't check table, column or the procedures permission specified in the table.
Database Backup Program (mysqlhotcopy) - mysqlhotcopy is a perl script. It was written and provide by Tim Bunce. It's using method of FLUSH_TABLE or LOCK_TABLE to make a backup for database. This is a quick way to create a backup for database or individual tables but it can only run on the same machine which originally use for backup where the database directory are located.
Convert Tables to use a given storage engine (mysql_convert_table_format) - mysql_convert_table_format change the table in the database using a specific storage engine. It only can be use if the DBI and DBD:mysal Perl modules is installed. (MySQL, 2010) (Using mysqldump to back up your MySQL database, 2010)
CHAPTER 3 : OPTIMIZATION
This chapter describes and explains several ways to optimize MySQL and provide example. To understand how and what to optimize the entire system, it required understanding and knowledge for optimization so it is a complex task. Although it perform some of the local optimization that doesn't require a lot of knowledge but the better optimization of the system user want to optimize, the more user need to be understand.
The fast speed is the important factor of basic design of a system. User must understand what kind of processing of the system is doing and it's bottlenecks. System bottleneck can be cause from these reasons:
Disk seeks: it need time to search for a data. With latest disks, the average time is usually lower than about 10sec, in theory we can do about 100 time seeking in a second This time increase is slowly with new disks and it is difficult for a single table optimization. The way to improve the search time is to distribute data into multiple disks.
Disk reading and writing: we need to read the data when the disc is in a right position. With the latest disks, single disk delivers throughput can be at least 10-20mb/s. This is easier to optimize rather than search, because you can read from multiple disks in parallel.
CPU cycles: When the data is in primary memory, we need to process it to get the result. Having small table will face the limiting factor but the speed will be fast if compare with large table because large table use more memory space.
Memory bandwidth: the bandwidth of the primary memory can become a bottleneck if needs more data that to fit into the CPU cache. Although it is not a common issue but it still need to be aware of. (MySQL, 2010)
Transactional and non-transactional tables can be work on MySQL. MySQL will follow the following rules to make the non-transactional table more efficient in work:
All columns have a default values
If there is an incorrect or out of range value insert into a column, MySQL will not report an error instead set the column as "best possible value".
All calculated expression will return a value instead to present an error message (MySQL, 2010)
Due to implement various parts of standard SQL into all SQL servers, users need to write portable database application. It can be very simple to achieve portability by using insert and select. But with thorough implementation of portability the more knowledge required. If users want to create a application to work fast with several databases in parallel, it become more difficult. All system have weaknesses including database system. That is because different design of the database leads to different behaviors. In order to make complex application to achieve portability, users need to understand which SQL server must work together and determine which features support by the server. User can use crash-me to program the search, type and restriction function in database server. Although crash-me won't check for every possible feature but is still reasonable to use.
If users intend to design database independency, users need to understand each SQL server's bottlenecks. MySQL is very fast in retrieve and update the line for the MYISAM table but when deal with writing and reading in the same time in a same table, it will slow down the performance. In general transactional database systems, that is not very good in generating summary tables from the log table because row locking become useless in this case. So in this case, if user implement some specific feature to given database system, user should also code the same feature as an alternative method to other SQL servers. Although the alternative method will slow down the performance but at least it allow the other servers perform the same task.
If the high performance is more importance than other feature such as a web application, users can create an application layer that cache the output then will give user a higher performance. Users can keep refreshing the cache by let the old result to expire. This method provide a way to handle high load spikes. Users can randomly increase the sizes of cache and manage a higher expiration timeout until everything gets back to normal.
MySQL provide an application called MySQL Benchmark Suite. This application is use to tell user the operation provide by the SQL implementation performs good or worst. Users can get a better idea to contribute the code and result by referring the benchmark suite.
Benchmark is run on single threaded, so it measure the performed operation's minimum time. There are following requirement must be exist to use benchmark suite:
Users must download MySQL Benchmark suite that provided my MySQL source distributions.
DBI must installed because the scripts of MySQL Benchmark are written in PERL and use Perl DBI to access database servers. (MySQL, 2010) (MySQL Server Benchmarking 101, 2010)
3.1 Optimizing SQL Statement
The more complexity of the permission setup that user use, the more overhead that user will get. To overcome this problem, using the simpler permission can overcome the high overhead by reducing permission-checking when the client run the statements. For an example, if there no any table-level and column-level permission, server will no need to check for the table's contents.
The following statement are mostly use for reduce the overhead:
Speed of Select Statement: To increase the speed of select….where statement, add an index if possible. Index should be use as references between different table.
Range Optimization: There are 2 optimization. First is range access method in single-part indexes and another one is multi-part indexes. For single part, corresponding conditions in the WHERE clause is representing the index value intervals, so we talk about the range condition but not the intervals. For multiple-part index, it is an extension of range condition for single-part. For multi-part indexes, the index on the index line is restricted within the scope of conditions that one or several key tuple intervals. A set of key tuples define a key tuple interval using sorting from the index.
Index merge Optimization: the method of Index Merge is to merge the result into one and get row with several range scans. This method can produce unions, intersections, or unions-of -intersection. This method only can access in a single table.
LIMIT Optimization: Normally, MySQL is perform a full table full scan. But with LIMIT row_count, MySQL will ends the scan once it found the first row_count rows of the sorted result. It is very efficient. (MySQL, 2010) (MYSQL SELECT statement 101, 2010)
3.2 Optimization and Indexes
- Column Indexes
All MySQL data types can be indexed. Correlation-based index of the column is the best way to improve the performance of SELECT operation. The maximum of index length and the maximum number of indexes for each table represent per storage engine. All storage engines support a total index length not less than 256 bytes and not less than 16 indexes for each table. (MySQL, 2010) (Simple Automation - Examining Your MySQL Index Usage, 2010)
- Multiple-Column Indexes
Composite indexes can be created via MySQL. An index can include up to 16 columns. For some of the data types, user can index a prefix of the column. A multi-columns indexes consider as a sorted array that the index contains the column value by connecting to create value. The benefit of using multi-column is even users do not specify the values of the other column, the queries will also perform fast when user specify a known quantity for the 1st column of the index with WHERE clause. (MySQL, 2010) (Simple Automation - Examining Your MySQL index Usage, 2010)
-MYISAM Index Statistics Collection
For optimize the indexes, storage engine will collect statistics about tables. Table statistics are based on the value of group which is in the set of rows with the same key prefix value. What the optimizer concern is an important statistic is the average of the value group size. But the usage of the index become useless when the average of the value group size increase, for a better optimization way, it is best if each of the index value is targeting to the small number of rows in the table. (MySQL, 2010) (Simple Automation - Examining Your MySQL Index Usage, 2010)
3.3 Optimizing Database Structure
To have a nice database structure, the priority is design the table to minimize their space on the disc as possible. This can greatly improve the performance by reduce the large number of data write to or read from the disc. Smaller table are always use less primary memory space to perform task. It can be result to a faster process speed if reduction for the table data.
MySQL provide quite a lot of table types and row formats. For each table type and row formats, users can select different indexing method to be use. By choosing a appropriate table format will greatly improve in the performance of an application or system.
There are techniques to making the data as small as possible show below:
Use the most appropriate data type. For example, use a MEDUIMINT is always better then use an INT because MEDIUMINT reduce almost 25% of space usage.
If possible, try not to declare column as NULL. But it doesn't encourage user not to use NULL. If really needed, just use it. With not NULL, it increase the perform speed and save one bit for each column.
For MyISAM table, use a fixed-size row format if do not have any variable-length column. It increase the performance speed but waste space.
Try to make the main index of a table as small as possible. It increase the identification for each row for faster and easier.
Create an index when only you need it. Index is good in retrieve data but bad in perform fast storing data.
Try to avoid create too many table in the same database. Too many MYISAM table in a same database will causes open, close and create operation become slow. It will make overhead if execute a same operation like SELECT on many different tables because the cache become full. It can be solve by increasing the number of entries permitted in the table cache.
(MySQL, 2010) (Cardinality in MySQL Data Modeling, 2010)
3.4 Optimizing the MySQL server
After I research various journals from the internet, I found that most of the testing to optimize the MySQL server as based on Linux with the MySQL Benchmark (introduce in last chapter). In linux, always choose PGCC and -O3 to compile the server. You will need about 200mb of memory space to compile because to make all function inline, we need PGCC and PGCC require quite a lot of memory to do this. To optimize the MySQL server, there is various of ways to do it. Way are show below:
System Factors and Startup Parameter Tuning : We start from the system-level factor because the earlier we made the decision, the higher performance boost we can achieve. In other cases, as long as sufficient meet the quick look. However, it is always a good choice to feel the upgraded system performance by changing the factor
How MySQL uses Memory: MyISAM key buffer is sharable for all threads. Key buffer is determined by key_buffer_size variable. Server will use other buffer when only it is needed. Threads are use to control the client's connection by using some thread-specific space. A same base memory is share by all threads. When no longer needed a thread, the memory allocated to it will be released and returned back to the system unless the thread will be remain in the thread cache. In this case, the memory is still in use. To understand the way how the MySQL use memory, we will able to optimize the usage without waste.
Disk Issues: Disc seeks are alway a big bottleneck in optimization. When the amount of data begin grow large, it become more obvious. To minimize this problem, always use a low seek times when multiple disc seek to read or write. To increase the disk spindles times by either symbolic link the files or striping the disc. Using RAID 0+1 to achieve the reliability if the finance is adequate. But for better option is to changing the RAID level adaptively according to the critical type of data is.
Some h/w or OS architectures support more than 4KB memory pages. This kind of Large Page Support is depending on the hardware and OS architecture. By using Large Page to improve the performance if the application manages a lot of memory accesses. It helps to reduced Translation Lookaside Buffer lost. But currently, Large Page only support on Linux. But the Linux's kernel must support Large Page that only can use it and configure the Huge TBL memory pool. (MySQL, 2010)
CHAPTER 4: LANGUAGE STRUCTURE
We will discuss following writing rules of SQL elements when using MySQL:
Identifier such as rows, columns and database
Reserved words.
System variables and User-defined variables
4.1 Schema Object Names
In MySQL, identifiers are table, index, database, alias, view, partition, stored procedure and column. We are going to discuss the usage and verify the identifier. Identifier could be referenced or non-referenced. If the identifier is a reserved word or is a special character, so user have to refer whenever we need to use it.
Identifier Qualifiers
MySQL allows names that implement in one or more identifiers. Part of a multi-part name must have a period (".") characters to separate it. As a limited impact on the environment, making the final interpretation of multiple components identified as the initial part of the name
Identifier Case Sensitivity
within MySQL, the database directory is corresponding inside data directory. Each table in a database corresponds to more than one file (depending on storage engine) within the database directory. Therefore, the underlying operating system plays the case sensitivity of the tables name and database. It means window doesn't responsible to the tables names and database case sensitive and Unix responsible to case sensitive for example, Mac OS X.
Function Name Parsing and Resolution
MySQL 5.0 provide native function, stored functions and user-defined functions. Now we learn how server understands a name is declaring as an identifier or function call, and how the server know which function need to be call when different type of function exit with a given name.
a. Built-in Function Name Parsing: default rules used by parser to parsing names of built-in functions. These rules can be changed to active IGNORE_SPACE mode. When the parser meet a word which is a name of built-in function, it have to define the name whether is a function call or a reference or a non-expression refer as a identifier such as column or table name.
b. Function Name Resolution: there are 3 rules of function name resolution, built-in function and user-defined function, built-in function and stored function and user-defined function and stored functions.
For build-in functions, create UDF (user-defined function) can be the same name as build-in function but UDF cannot invoked. It is because the parser will refer to the built-in function.
For built-in function and stored function, it can be create a stored function's name same as the built-in function but have to qualify it with a database name if wanted to invoke.
For user-defined function and stored function, both functions share the same namespace, so the name of UDF cannot be same as stored function. (MySQL, 2010)
4.2 Reserved Words
In MySQL, reserved words such as DELECT, SELECT or INSERT are using as identifier such as column names and table.
Exception: if a word is qualified name as an identifier, even it is a reserved word but also no need to quote.
Identifier can be a name of built-in function but may be use with care. For example, column name can be accepted by count. However by default, it allow the spaces between the function and the following "(" character. This requirement is to identify the name of the parser used in a function call or a non-functional aspect.
The following table consists of reserved words in MySQL 5.0:
4.3 User-Defined Variables
User can refer the user-defined variable in another statement after he/she store a value in a user-defined variable in one statement. This enables user to pass values from one statement to another statement. A user-defined variable create by one user cannot be view by another user, connection-specific. The variables will release to the client automatically once the client is online.
Example: user-defined variable by using set
User-defined variables also can be use on non-set statement. For example:
Value can be assigned by user-defined variable from a limited set of data types such as decimal, integer, binary string, floating point, binary or NULL value.the actual value of the decimal value assigned doesn't retain the accuracy or scale. A value of a type with non-allowable can be convert into allowable type.
If a user-defined variable is assigning with character string value, then it will have string's characteristic set and collation. We use CAST() or +0 to assign a bit value as a number to a user-defined variable:
As a common rule, user should never assign a value to a user-defined variable and reading the value in the same statement. You may get the desired results, but is not guaranteed. User-defined variables involved the calculation of the order of the expression is uncertain and may change the statement given based on the included elements.
(MySQL, 2010)
CHAPTER 5 DATA TYPE
5.1 Data Type Overview
MySQL supports several types of data : date and time types, character (string) type and numeric type. Int this chapter, I will describe data type overview and information of particular data type (numeric, date, time and string). (MySQL, 2010) (MySQL, A visual quickstart guide, 2003)
5.2. Numeric, Date and Time and String Types
i. String type:
Standard SQL numeric data types are support by MySQL. Types are included INTEGER, DECIMAL, NUMERIC and DECIMAL. MySQl also support REAL, DOUBLEPERCISION and FLOAT. This following table shows the range of each integer types:
(MySQL, 2010) (MySQL, A visual quickstart guide, 2003)
ii. Date and Time
Types of date time are DATA, DATETIME, TIME, YEAR and TIMESTAM. Each type has a range of legal values, so example a "zero" value that can be used when user specify an invalid value that cause MySQL couldn't represent. The following table show the format of the "zero" value for all type:
(MySQL, 2010) (MySQL, A visual quickstart guide, 2003)
iii. String type
String types are VARCHAR, CHAR, VARBINARY, BINARY, TEXT, ENUM, SET and BLOB. For char and varchar, they are similar. The following table will describe the different between these 2 types of string:
(MySQL, 2010) (MySQL, A visual quickstart guide, 2003)
For binary and varbinary, they are also similar as char and varchar. But the different from char and varchar is they consist of binary string.
For blob and text, they have the same characteristic which is a large object that can carry large amount of data. Each of them have 4 types. For blob: mediumblob, blob, longblob and tinyblob. For text: mediumtext, longtext, tinytext and text.
For enum, it is a list of values from the allowed list to create table columns in the specification explicitly chosen a value of string object. For set, it is a string object that can contain zero or more value, each must be created fomr the specified table, allowing the value of the list. Set column values is to set the specified number of the members by separate them with commas (","). (MySQL, 2010)
CHAPTER 6 MySQL Functions and Operator
SQL statements use expression in several points, such as in a WHERE clause of a SELECT statement, in the ORDER BY or HAVING clause of a SELECT, DELETE or UPDATE statement. Operators, user-defined function, stored function, built-in function NULL, column values and literal values can be use to write expression.
6.1 Operator and Function reference
The following table shows the functions and Operators:
(MySQL, 2010)
6.2 Control Flow Function
The following table shows the Flow Control Operator:
(MySQL, 2010)
6.3 String, numeric, date and times function
The following table shows the String Function:
(MySQL, 2010)
The following table shows the date and times function:
(MySQL, 2010)
The following table show the numeric functions:
(MySQL, 2010)
6.4 Encryption and Compression of functions
Most of the encryption and compression functions will return a string as a result that contain arbitrary byte values. To store the result, a column with VARBINARY or BLOB is needed.
The following table shows the Encryption and Compression functions:
(MySQL, 2010)
CHAPTER 7 EVALUATE BETWEEN MySQL AND MICROSOFT OFFICE ACCESS
Microsoft Access Database is less stable and slower database if compare with MySQL Database. MySQl is free to use because it is open-source. Create the database for web application, the security and stability of MySQL is almost unbeatable. The response times of the benchmarked mySQL query are unparalleled.
MySQL database is the more popular and always the first choice for web application rather than choosing Microsoft Access Database. Due to the high security, MySQL database is harder to hack by hacker if compare with Microsoft Access Database. Therefore, MySQL suitable for Online Banking system, online shopping system and various different of system that concern with money trading.
MySQl also provide several nice and free GUI Frontend tools that can be use in MySQL database. The 2 nice GUI are PHP myAdmin and another one is MySQL control center and also what we call MySQLCC. MySQLCC is easier use and intuition use than PHP (due to different people favorite). It is fast and simple to install to different type of operating system that make it the first choice for web developer.
How if currently using Microsoft Access Database and wish to change to MySQL database? Web develop can migrate the database table structure and data into MySQL database. To migration Microsoft Access Database to MySQL Database, web developer need ODBC (Open Database Connectivity) tool which we frequently use to open the connection between application and Microsoft Access Database. An interpreter will be act by ODBC to transfer the table and column into MySQL database with a right format (MySQL Open Source standard format) between Microsoft Access Database and MySQL Database.
Why MySQl Database is better than Microsoft Access Database? In nowadays, business are looking for the database which is ease of use, high-level reliable, high-level flexibility and fast database performance speed and the most importance is inexpensive, costless. MySQL database provide all those advantage against with Microsoft Access Database. MySQL Database is the only one on the database market that doesn't need to put in too much investment on it. All these advantage make MySQL Database become the best choice for the web developer rather than choosing Microsoft Access Database.
CHAPTER 8 CONCLUSION
By doing this seminar, I understand that MySQL is a powerful database management system. It boosts the dynamically served website which heavy traffic and frequently visited by the public. MySQL rely on by most of the web application and web pages by storing the data contents which will read by web server to develop webpage for the visitor to read dymanically.
Once a web page is visited by visitor, MySQL database server will have to assembly, manufacture and service the website at least one time for the next reader. So, the MySQL server performance quality and speed is very importance. If it is slow, reader might cannot get the information from the web site. The performances are become very critical especially during the peak and overload web host. Optimization become a importance feature to improve the MySQL database system. Therefore, Optimize MySQL skills is needed.
MySQL Database System had prove that it can help business save up to 90% cost. It mainly save costs on Total Cost of Ownership of the software in license cost (up to 90%), reduce the downtime of system (about 60%), low expenditure of hardware (about 70%) and support cost from web developer, administration and administration reducing (about 50%). It hardly makes business team not to choose it due to the advantages.
The widely advantage advantages provided by MySQL not only low cost, it also include ease of deploy and installation, easy to administer to manage, great performances, high in reliability and availability (about 99.999% of the availability), embedded library and independent platform (support various of OS such as Unix, window and so on).
Finally, I want to say is MySQL Database System is a best choice no matter is for business, financial, banking, customer services or government application or web services.
CHAPTER 9 REFERENCES
MySQL, 2010, MySQL 5.0 Reference Manual
http://dev.mysql.com/doc/refman/5.0/en/index.html
Sean Hull, 12 Oct 2010, MySQL Server Benchmarking 101 <http://www.databasejournal.com/features/mysql/article.php/3907091/MySQL-Server-Benchmarking-101.htm>
Jason Gilmore, 29 Sept 2010, Using MySqldump to Back Up Your MySQL Database
<http://www.databasejournal.com/features/mysql/article.php/3905786/Using-mysqldump-to-Back-Up-Your-MySQL-Database.htm>
Chris Schneidier, 2 Nov 2010, Simple Automation - Examining Your MySQL Index Usage
<http://www.databasejournal.com/features/mysql/article.php/3910466/Simple-Automation---Examining-your-MySQL-Index-Usage.htm>
Rob Gravelle, 8 Oct 2010, Cardinality in MySQL Data Modeling
<http://www.databasejournal.com/features/mysql/article.php/3906761/Cardinality-in-MySQL-Data-Modeling.htm>
Rob Gravelle, 27 Aug 2010, MySQL SELECT Statement 101
<http://www.databasejournal.com/features/mysql/article.php/3898816/MySQL-SELECT-Statement-101.htm>
Ullman L., 2003 , MySQL, A visual quickstart guide.
<http://docs.google.com/viewer?a=v&q=cache:c-G0xdYFOIJ:www.idready.org/rdbms /FieldDataTypes.doc+mySql+data+type&hl=en&gl=my&pid=bl&srcid=ADGEESiI6LOapcoZX9ZZd8mNd6bz2YUZ5m9nP36ZV8nD7BYs_-2iJwRhafS16g4Q0sNwo1ClvN-KZ1NUihx0UUxgOcGhzFcldTM44b1yfzxMWH4YHzypp5Ap1Lm4d1ZVA11fS67jsk&sig=AHIEtbTuJLtem914so0zqKGwxteOo0uWrg>
