MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is a free, widely used SQL engine. MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. Since its introduction in 1995, MySQL quickly became the world’s most popular database management system. It can be used as a fast database as well as a rock-solid DBMS using a modular engine architecture. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation.
Mysql tips and tricks for getting performance in Web Application Development
In the early stage of development you should be aware of expected number of users coming to your application. If you expect many users, you should think big from the very beginning, plan for replication, scalability and performance.
- Do Not Edit Dump files: If you have ever seen the dump files created by mysqldump, they look an awful lot like regular, harmless text files. But for anyone who has ever tried to edit them, they have quickly learned that they are anything but a text file. Most people edit them in a standard text editor, and then the corruptions start to appear. The only guaranteed way to avoid problems is to leave the dump files alone.
- Picking the right data types can help by ensuring that the data we are storing makes the database as small as possible.
- Produce terminal-friendly output: Occasionally you may be using the command line and find yourself limited by the size of either the table you’re getting results from or by the size of the terminal window you’re using.
- MyISAM Block Size : One of the better kept secrets is a setting for block sizes in the indexes of MyISAM tables. The setting, myisam_block_size can be found in the MYI files in the key buffer, as well as the disc. It has a default value of 1k, which is quite small to be optimal on a modern system. Most file systems will have used a larger size block. And we know that writing a single index block will require a read and then a write. The operating system will never have to write to the underlying disc, just so long as the block size is either equal to, or greater than filesystem block size.
- Use Query Cache The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
- Loading .CSV file data into a table using the command line: MySQL has a very simple way to load .csv file data into tables.
- Identify slow queries just because MySQL does not track and record slow queries by default that doesn’t mean that you can not make it do your bidding for you. As we have learned through these tips, MySQL has many functions outside of the default settings that can help the user greatly.
- Turning on delay_key_write: By default delay_key_write is turned off. The reason for this is because if you experienced a crash mi-project, you could end up with a corrupted database. So why would you want to turn it on? Simple. Because turning delay_key_write on ensures that the database will not flush the MyISAM key file after every single write. So, if you are doing another write in the near future, you will be saved quite a lot of time. Turning on delay_key_write is version specific. To see the differences in turning it on for your version use the official MySQL site manual.
- “Store IP Addresses as UNSIGNED INT” : A quick and priceless tidbit. Storing IP addresses this way actually allows MySQL to store them as integer values. Use INET_ATON() to convert the target IP to an integer. Anyone familiar with PHP might recall a similar function called ip2long. John Bafford has spent too much time with the most complete analysis on this technique, including every last detail down to saving money on storage costs.
- Use Your Indexes to Create Tables: Save time in this process by killing two birds with one stone. An index can actually be created while the table itself is being created.
Related Keywords: Mysql, Mysql database tips, Mysql tips and tricks, Tips and Tricks to optimize Mysql, mysql performance tricks, mysql tutorials