MySQL is the fastest growing open-source relational database management system with 100 million downloads till date. It is a popular choice of database for use in web applications and is currently used by many large websites, including Facebook, Twitter, Wikipedia, Flickr, YouTube etc. Now, let’s find out top 10 interesting facts and tips about MySQL.
Interesting facts and tips about MySQL
1. MySQL supports up to 64 indexes per table. Each index may consist of 1 to 16 columns. The maximum index size is 1000 bytes (767 for InnoDB).
2. The maximum size of a row in a MySQL table is 65,535 bytes. And the maximum value of Signed Integer is 2,147,483,647 and the value of Unsigned Integer is 4,294,967,295. In a mixed table with both CHAR and VARCHAR, MySQL will change the CHAR’s to VARCHAR’s.
3. If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as “_rowid” in SELECT statements.
4. To change the value of the AUTO_INCREMENT, use “ALTER TABLE
5. To restrict MySQL from being accessed publicly, use “skip-networking” option in the config file. When it is enabled, MySQL only listens to local socket connections and ignores all TCP ports. And “bind-address” parameter which is set to “127.0.0.1” restricts the MySQL to be accessible only by the localhost.
6. If the MySQL has many connects established (i.e. a website without persistent connections), you can improve the performance by setting thread_cache_size to a non-zero value. 16 is a good value to start with. Increase the value until your threads_created do not grow very quickly.
7. NO_AUTO_VALUE_ON_ZERO suppresses auto increment for 0. Only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table’s AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.)
8. The configuration options “innodb_analyze_is_persistent”, “innodb_stats_persistent_sample_pages” and “innodb_stats_transient_sample_pages” provide improved accuracy of InnoDB index statistics, and consistency across MySQL restarts. InnoDB precomputes statistics that help the optimizer decide which indexes to use in a query, by sampling a portion of the index. You can adjust the amount of sampling that InnoDB does for each index. The resulting statistics can now persist across server restarts, rather than being recomputed (and possibly changing) due to restarts and some runtime events. The more accurate statistics can improve query performance, and the persistence aspect can keep query performance stable. When the persistent stats feature is enabled, the statistics are only recomputed when you explicitly run ANALYZE TABLE for the table.
9. InnoDB frees up the memory associated with an opened table to ease the memory load on systems with huge numbers of tables. An LRU algorithm selects tables that have gone the longest without being accessed. To reserve more memory for open tables, increase the value of the –table_definition_cache=# configuration option.
10. Set table_cache parameter to match the number of open tables and concurrent connections. Watch the open_tables value and if it is growing quickly you need to increase the size of “table_cache”. And for the parameter “open_file_limit” set this limit as 20+max_connections+table_cache*2. If you have complex queries “sort_buffer_size” and “tmp_table_size” are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points.
Note: These are “per connection” values. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL needs to do a sort, be careful not to run out of memory.
