Quick Reference of MySQL Data Types

Once you have identified all of the tables and columns that the database will need,you should determine each field’s MySQL data type.
When creating the database, as you will do in the next chapter, MySQL requires that you define what sort of information each field will contain. There are three primary categories, which is true for almost every database software:

• Text
• Numbers
• Dates and times

Within each of these, there are a number of variants—some of which are MySQL-specific—you can use. Choosing your column types correctly not only dictates what information can be stored and how, but
also affects the database’s overall performance.

The most
of the available Data types for use with MySQL
databases.

MySQL Datatypes
Ty p e	S i z e	D e s c r i p t i o n
CHAR[Length]	Length bytes	A fixed-length field from 0 to 255 characters long.
VARCHAR(Length)	String length + 1 bytes	A fixed-length field from 0 to 255 characters long.
TINYTEXT	String length + 1 bytes	A string with a maximum length of 255 characters.
TEXT	String length + 2 bytes	A string with a maximum length of 65,535 characters.
MEDIUMTEXT	String length + 3 bytes	A string with a maximum length of 16,777,215 characters.
LONGTEXT	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters.
TINYINT[Length]	1 byte	Range of -128 to 127 or 0 to 255 unsigned.
SMALLINT[Length]	2 bytes	Range of -32,768 to 32,767 or 0 to 65535 unsigned.
MEDIUMINT[Length]	3 bytes	Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.
INT[Length]	4 bytes	Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.
BIGINT[Length]	8 bytes	Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.
FLOAT	4 bytes	A small number with a floating decimal point.
DOUBLE[Length, Decimals]	8 bytes	A large number with a floating decimal point.
DECIMAL[Length, Decimals]	Length + 1 or Length + 2 bytes	A DOUBLE stored as a string, allowing for a fixed decimal point.
DATE	3 bytes	In the format of YYYY-MM-DD.
DATETIME	8 bytes	In the format of YYYY-MM-DD HH:MM:SS.
TIMESTAMP	4 bytes	In the format of YYYYMMDDHHMMSS; acceptable range ends inthe year 2037.
TIME	3 bytes	In the format of HH:MM:SS
ENUM	1 or 2 bytes	Short for enumeration, which means that each column can haveone of several possible values.
SET	1, 2, 3, 4, or 8 bytes	Like ENUM except that each column can have more than one ofseveral possible values.

 

Many of the types can take an optional Length attribute, limiting their size (the square brackets, [], indicate an optional parameter to be put in parentheses, while parentheses themselves indicate required arguments).
Further, the number types can be UNSIGNED—limiting the column to positive numbers or zero—or be defined as ZEROFILL, which means that any extra room will be padded with zeroes (ZEROFILLs are also automatically UNSIGNED).You’ll primarily use the DATE and TIME fields without modification, so you need not worry too much about their intricacies. There are also two extensions of the TEXT types that result in a different behavior—ENUM and SET—which allow you to define a series of acceptable values when creating the table. An ENUM field can have only one of a possible several thousand values, while SET allows for several of up to 64 possible values.
There are two caveats with ENUM and SET: These types are not supported by other databases, and their usage undermines normalization.

 

MySQL Server Performance Tips

Specific Query Performance Tips (see also database design tips for tips on indexes):

1. Use EXPLAIN to profile the query execution plan
2. Use Slow Query Log (always have it on!)
3. Don’t use DISTINCT when you have or could use GROUP BY
4. Insert performance
1. Batch INSERT and REPLACE
2. Use LOAD DATA instead of INSERT
5. LIMIT m,n may not be as fast as it sounds
6. Don’t use ORDER BY RAND() if you have > ~2K records
7. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
8. avoid wildcards at the start of LIKE queries
9. avoid correlated subqueries and in select and where clause (try to avoid in)
10. no calculated comparisons — isolate indexed columns
11. ORDER BY and LIMIT work best with equalities and covered indexes
12. separate text/blobs from metadata, don’t put text/blobs in results if you don’t need them
13. derived tables (subqueries in the FROM clause) can be useful for retrieving BLOBs w/out sorting them. (self-join can speed up a query if 1st part finds the IDs and use it to fetch the rest)
14. ALTER TABLE…ORDER BY can take data sorted chronologically and re-order it by a different field — this can make queries on that field run faster (maybe this goes in indexing?)
15. Know when to split a complex query and join smaller ones
16. Delete small amounts at a time if you can
17. make similar queries consistent so cache is used
18. Have good SQL query standards
19. Don’t use deprecated features
20. Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index_merge should pick stuff up.
21. Don’t use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you need it for the total # of rows, use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()
22. Use INSERT … ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT
23. use groupwise maximum instead of subqueries

Scaling Performance Tips:

1. Use benchmarking
2. isolate workloads don’t let administrative work interfere with customer performance. (ie backups)
3. Debugging sucks, testing rocks!
4. as your data grows, indexing may change (cardinality and selectivity change). Structuring may want to change. Make your schema as modular as your code. Make your code able to scale. Plan and embrace change, and get developers to do the same.

Network Performance Tips:

1. Minimize traffic by fetching only what you need.
1. Paging/chunked data retrieval to limit
2. Don’t use SELECT *
3. Be wary of lots of small quick queries if a longer query can be more efficient
2. use multi_query if appropriate to reduce round-trips

OS Performance Tips:

1. Use proper data partitions
1. For Cluster. Start thinking about Cluster *before* you need them
2. Keep the database host as clean as possible. Do you really need a windowing system on that server?
3. Utilize the strengths of the OS
4. pare down cron scripts
5. create a test environment
6. source control schema and config files
7. for LVM innodb backups, restore to a different instance of MySQL so Innodb can roll forward
8. partition appropriately
9. partition your database when you have real data — do not assume you know your dataset until you have real data

MySQL Server Overall Tips:

1. innodb_flush_commit=0 can help slave lag
2. Optimize for data types, use consistent data types. Use PROCEDURE ANALYSE() to help determine the smallest data type for your needs.
3. use optimistic locking, not pessimistic locking. try to use shared lock, not exclusive lock. share mode vs. FOR UPDATE
4. if you can, compress text/blobs
5. compress static data
6. don’t back up static data as often
7. enable and increase the query and buffer caches if appropriate
8. config params — http://docs.cellblue.nl/easy_mysql_performance_tweaks/ is a good reference

Config variables & tips:

1. use one of the supplied config files
2. key_buffer, unix cache (leave some RAM free), per-connection variables, innodb memory variables
3. be aware of global vs. per-connection variables
4. check SHOW STATUS and SHOW VARIABLES (GLOBAL|SESSION in 5.0 and up)
5. be aware of swapping esp. with Linux, “swappiness” (bypass OS filecache for innodb data files, innodb_flush_method=O_DIRECT if possible (this is also OS specific))
6. defragment tables, rebuild indexes, do table maintenance
7. If you use innodb_flush_txn_commit=1, use a battery-backed hardware cache write controller
8. more RAM is good so faster disk speed
9. use 64-bit architectures
10. –skip-name-resolve
11. increase myisam_sort_buffer_size to optimize large inserts (this is a per-connection variable)
12. look up memory tuning parameter for on-insert caching
13. increase temp table size in a data warehousing environment (default is 32Mb) so it doesn’t write to disk (also constrained by max_heap_table_size, default 16Mb)
14. Run in SQL_MODE=STRICT to help identify warnings
15. /tmp dir on battery-backed write cache
16. consider battery-backed RAM for innodb logfiles
17. use –safe-updates for client
18. Redundant data is redundant

Storage Engine Performance Tips:

1. InnoDB ALWAYS keeps the primary key as part of each index, so do not make the primary key very large
2. Utilize different storage engines on master/slave ie, if you need fulltext indexing on a table.
3. BLACKHOLE engine and replication is much faster than FEDERATED tables for things like logs.
4. Know your storage engines and what performs best for your needs, know that different ones exist.
1. ie, use MERGE tables ARCHIVE tables for logs
2. Archive old data — don’t be a pack-rat! 2 common engines for this are ARCHIVE tables and MERGE tables
5. use row-level instead of table-level locking for OLTP workloads
6. try out a few schemas and storage engines in your test environment before picking one.

Database Design Performance Tips:

1. Design sane query schemas. don’t be afraid of table joins, often they are faster than denormalization
2. Don’t use boolean flags
3. Use Indexes
4. Don’t Index Everything
5. Do not duplicate indexes
6. Do not use large columns in indexes if the ratio of SELECTs:INSERTs is low.
7. be careful of redundant columns in an index or across indexes
8. Use a clever key and ORDER BY instead of MAX
9. Normalize first, and denormalize where appropriate.
10. Databases are not spreadsheets, even though Access really really looks like one. Then again, Access isn’t a real database
11. use INET_ATON and INET_NTOA for IP addresses, not char or varchar
12. make it a habit to REVERSE() email addresses, so you can easily search domains (this will help avoid wildcards at the start of LIKE queries if you want to find everyone whose e-mail is in a certain domain)
13. In 5.1 BOOL/BIT NOT NULL type is 1 bit, in previous versions it’s 1 byte.
14. A NULL data type can take more room to store than NOT NULL
15. Choose appropriate character sets & collations — UTF16 will store each character in 2 bytes, whether it needs it or not, latin1 is faster than UTF8.
16. Use Triggers wisely
17. use min_rows and max_rows to specify approximate data size so space can be pre-allocated and reference points can be calculated.
18. Use HASH indexing for indexing across columns with similar data prefixes
19. Use myisam_pack_keys for int data
20. be able to change your schema without ruining functionality of your code
21. segregate tables/databases that benefit from different configuration variables

Discussion about optimize MySQL to handle a high traffic website.

MySQL settings, many concurrent users. 

I run a site for a client that has over 3000 users that log in for about 5-7 hours  per day each. So, at peak times, we have to handle about 2000 concurrent users. When configured correctly, PHP and MySQL can handle this load wonderfully on fairly cheap Intel architecture. First off, hardware.

1) It is better to have 2 separate servers for Apache/PHP and MySQL with the Linux of your choice. 

2) Try not to run too much else on either box; leave the resources for Apache/PHP and MySQL. 

Here are the specs on each box in my config: 

1) Apache/PHP: Pentium 3, 600 MHZ, 512 megs ram. 

2) MySQL: Dual Pentium 3, 750 MHZ (1500 MHZ total), 2 gigs ram. 

The reason for this configuration is that it is very database heavy; it is a member’s only web site with username and password required for login, fully personalized. It is an online school, so each student has their suite of tools for attending school, their courses, report cards, time logging, and much more. Teachers have web based tools to create their courses, including lessons, text to speech audio, and more. 

1) PHP coding: be sure to use persistent connections! Opening and closing a connection from your Apache/PHP box to your MySQL box is a very heavy load. By using persistent connections, a high capacity site will open connections and share them to exchange data rather than opening a connection on each page request, sending the data, then closing, and repeating that process at least once for every user click! Be sure to use "mysql_pconnect" instead of "mysql_connect" and also that appropriate changes are made in "php.ini" or overridden by using the command "ini_set". 

You can find more documentation on doing this at the php web site. 

2) Apache set up ("httpd.conf"): I've changed these various settings, and played with them until they seem to keep the most "idle %" reported in "top". 

   MinSpareServers 10 

   MaxSpareServers 20 

   StartServers 70 

   MaxClients 255 

3) Mysql set up ("my.cnf"). The MySQL config file, my.cnf. 

Here is what to add under the [mysqld] heading. The two lines, "max_connections" and "max_user_connections" are where the magic happens. Since your Apache/PHP box is connecting to MySQL, it appears as a single user. MySQL defaults to 1 max connection, with 1 max connection per user. The following lines make it so your Apache/PHP box can connect to your MySQL box up to the number you have set "MaxClients" to in the Apache config above. By using persistent connections, you can pretty much get Apache up, have it connect to MySQL upon start up, and just use the persistent connections to pass data between the two boxes rather than opening connections. Its much more efficient that way. 

set-variable = max_connections = 300 

(this must be higher than "MaxClients" set in Apache, or you won't fully maximize use) 

set-variable = max_user_connections = 300 

set-variable = table_cache=1200 

(Max number of tables in join multiplied by max_user_connections) 

A few other MySQL tunings: 

set-variable = max_allowed_packet=1M (sanity check to stop runaway queries) 

set-variable = max_connect_errors=999999 

(stop mysqld from shutting down if there are connect errors - this defaults to 1 error and mysqld stops!) 

MySQL vs. PostgreSQL

Which is better, PostgreSQL or MySQL? Both are excellent products with unique strengths, and the choice is often a matter of personal preference. Read on for a useful comparison of these two open source database systems.

Actually many people asked, “Which is the best PostgreSQL or MySQL?” The answer is always the same: “It’s a matter of preference.” You could ask many developers the same question, and their responses will all be different. Here is a comparison of MySQL and PostgreSQL databases, offered not for the sake of voicing my opinion, but to help you make your own decision.

Both systems have much to offer in terms of stability, flexibility, and performance. MySQL has features that PostgreSQL lacks, and vice versa. However, my primary focus is to help you determine which of the two databases to use in your own development.

MySQL has many different table types that support transactions and foreign keys and are compliant with ACID. However, some of the configurations of these table types are quite complex. Not many Web developers or programmers use the additional table types found in MySQL. With that said, let’s compare these two products.

When to use MySQL

Why would you use MySQL over PostgreSQL? First, we need to consider the needs of the applications in terms of database requirements. If I want to create a Web application and performance is an issue, MySQL will be my choice because it’s fast and designed to work well with Web-based servers. However, if I want to create another application that demands transactions and foreign key references, PostgreSQL is the choice. (No doubt, with InnoDB storage engine we can use the transactions as well as foreign key in MySQL 5.0)

I explained here difference as per my knowledge.

Some reasons for using MySQL over PostgreSQL:

• MySQL is relatively faster than PostgreSQL.
• Database design will be simpler.
• You can create a basic Web-driven Web site.
• MySQL’s replication has been thoroughly tested.
• There’s no need for cleanups in MySQL (Vacuum).

Some reasons for using PostgreSQL over MySQL:

• Complex database design
• Moving away from Oracle, Sybase, or MSSQL
• Complex rule sets (i.e., business rules)
• Use of procedural languages on the server
• Transactions
• Use of geographical data
• R-Trees (i.e., used on indexes)

Comparison

The comparsion of the newest, stable and production version of PostgreSQL 8.0 and MySQL 4.1 (MySQL has also development version 5.0).

	PostgreSQL 8.0	MySQL 5.0
Operating System	Windows, more than 2 dozen Unix-like operating systems (Linux, all BSDs, HP-UX, AIX, OS X, Unixware, Netware…)	Linux, Windows, FreeBSD, MacOS X, Solaris, HP UX, AIX, and many others.
ANSI SQL compliance	ANSI-SQL 92/99	Possible; user can run MySQL in more ANSI compatible (ANSI mode)
Performance	Slower	Faster
Sub-selects	Yes	Yes
Transactions	Yes	Yes
Database replication	Yes	Yes
Foreign key support	Yes	Yes
Views	Yes	Yes
Stored procedures	Yes (pl/SQL)	Yes (procedural)
Triggers	Yes	Yes
Unions	Yes	Yes
Full joins	Yes	No planned for 5.1
Constraints	Yes	No Planned for 5.1
Cursors	Yes	Partial (read only)
Procedural languages (PLs)	Yes PL/pgSQL, PL/Tcl, PL/Perl, PL/Python PL/PHP, PL/Java or user defined	Yes Supports stored procedures (persistent modules) languages as defined by ANSI SQL 2003
Vacuum (cleanup)	Yes	Yes by OPTIMIZE TABLE
Different table types	No (PostgreSQL has its own inbuilt table types and doesn’t use any alternative ones)	Yes MyISAM, InnoDB, MEMORY, BerkeleyDB, MERGE, Archive and NDB (Cluster), Federated (InnoDB has additional functionality)
ODBC	Yes	Yes
JDBC	Yes	Yes
Other APIs	Most of languages (i.e. Perl, C/C++, .NET, OLE-DB, Tcl/Tk, Python, PHP, …)	Most of languages
IPv6 support	Yes	No Planning in 5.1
WWW	- Homepage, - Manual, - Features	- Homepage, - Manual, - Features, - What’s new in 5.0,
Installation	PostgreSQL installation	MySQL installation
Download	Download page	Download page

MySQL Crash Recovery

MySQL Crash Recovery
MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things – MySQL Shutdown is unexpected and there could be various inconsistences. And this is not only problem as we’ll see.
MySQL has angel process mysqld_safe which will restart MySQL Server in most cases. It is great, unless you have run into some bug which causes it to crash again – such crashes qucikly following one another are kind of worse because they explore many less tested code paths in MySQL and so problem potential is larger.
So lets look at the problem which happen during the crash which might need to take care of or which may seriously affect MySQL Performance.
MyISAM Corruption - If you’re writing to MyISAM tables there is very large chance of them becoming corrupted during the crash. Note corruption may be hidden and do not expose itself instantly – you may notice wrong query results days after crash. Sometimes corrupted tables may be reason for further crashes or hangs, and corruption may spread itself further in the table. You probably do not want any of these so it is very good idea to run MySQL with myisam_recover option which will make sure all improperly closed MyISAM tables are checked first time it is accessed. This option is however rather painful to use with web applications – users may issue different queries which may trigger check/repair running for many tables at onces, which typically make system extremely slow and also can use up all allowed connections or run out of memory ( myisam_sort_buffer_size is normally set pretty lage). If this becomes the problem I use tiny script which moves out all MyISAM tables out of MySQL database directory, checks them with MyISAMchk and moves them back to running server. This looks scary but it works great – until table is checked and ready application gets error rather than stalling forever which allows application to become partially functional as soon as possible. This hack is needed only in some cases – in most cases using Innodb for tables which you need to be recovered fast is better solution.
Innodb Recovery – Unless you have some hardware problems (99%) or found new Innodb bug (1%) Innodb recovery should be automatic and bring your database to consistent state. Depending on innodb_flush_lot_at_trx_commit setting you may lose few last committed transactions but it is it. It is Performance of this process which may cause the problems. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file_per_table=1 your recovery speed will depend on number of Innodb tables you have, as well as many other operations, so beware.
Binary log corruption - Binary log may become corrupted and out of sync with database content. This will sometimes break replication but if you’re just planning on using binary log for point in time recovery it can go unnoticed. sync_binlog Is helping by syncing binary log, but at performance penalty. If using Innodb you also might with to use innodb-safe-binlog option in MySQL 4.1 so your Innodb log and binary log are synchronized. In MySQL 5.0 XA is taking care of this synchronization.
.frm Corruption – Few people know MySQL is not really ACID even with Innodb tables, at least not for DDL statements.
There is a chance of failing for example during CREATE statement with table created in Innodb dictionary but .frm not created or not completely written. Partially written .frm files or .frm being unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was added which reduces this problem as time window when it can happen is much less. Still if failure happens just during writting .frm file nasty things may happen, not to mention such potentially multiple operation DDL statements as RENAME TABLE – these are most vulnerable.
master.info corruption - If slave happens to crash you can also have relay logs corruption and master.info being corrupted. Not to mention MyISAM tables can contain partially completed statements as well as some of updates totally lost. The safe approach it to reclone the slaves if they crash or you can take the risks and try to continue. Sometimes you might be able to manually find appropriate position even if master.info file is out of sync but I would not be basing my failure handling scenarios.
Cold Start – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may reduce performance dramatically. So if you’re bringing server back after crash you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage engines it can be done by issuing large index scan queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can save these into .sql file and use –init-file to make sure it is run on startup. The other approach is to prime server with real servers (ie clone queries from other slave) before putting traffic to it.
In case application is not highly available so there is only one server you might with to start serving only some users initially (returning error to others) and gradually increase the load as server warms up. This may sound strange but makes a lot of sense as not only waiting for pages which never load is more frustrating for users than getting honest “try again later” message, but also – warmup takes longer time on extreme load.
Innodb statistics - Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table access after startup. This may take significant time if you have very large number of tables (Some users have hundreds of thousands of tables per database host). This one is pretty much part of cold start problems but I wanted to point out it separately. To warmup this data you might run select 1 from _table_ limit 1 for each table or any other statement – it is table open which is important.
There are other problems which you may experience related to MySQL Crash Recovery – Restoring data from backup, corrupted Innodb tablespace recovery etc but I should write about them some other time.

Reference by : http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/