Primary links
MySql Related
admin — Wed, 23/08/2006 - 10:33pm
Column types and attributes
1) Be familiar with MySQL's column types along with their ranges/lengths. You should use the smallest column type that will hold the data that you expect to store. Most of the time, you probably don't need INT, because MEDIUMINT, SMALLINT, or TINYINT have enough range. Using a smaller type saves space and speeds things up.
2) Declare all of your columns NOT NULL unless you need to store NULL values (NULL is not the same as 0 or the empty string). If you need to store NULL, you'll know. Again, NOT NULL saves space and speeds things up.
3) Making INT-family columns UNSIGNED will effectively double the positive range with the same storage requirement. For example, TINYINT's highest value is 127. TINYINT UNSIGNED's highest value is 255.
Indexes and optimization
1) First, I sometimes see people create tables like this:
Do not make a column KEY, UNIQUE, and PRIMARY KEY. In this case, KEY and UNIQUE don't help anything. The PRIMARY KEY is a key and it is unique. By specifying 3 indexes, it wastes space and slows down write operations.
2) Indexes/keys are the key (no pun intended) to fast queries (especially joins). The 3 types of indexes are PRIMARY, UNIQUE, and KEY/INDEX (same thing). It's hard to explain when and how to use indexes, so I'll just point you to some relevant pages in the MySQL manual: Speed of SELECT queries and How MySQL uses indexes.
3) When you have your indexes setup, use EXPLAIN on SELECT queries to make sure your indexes are actually being used.
1) Be familiar with MySQL's column types along with their ranges/lengths. You should use the smallest column type that will hold the data that you expect to store. Most of the time, you probably don't need INT, because MEDIUMINT, SMALLINT, or TINYINT have enough range. Using a smaller type saves space and speeds things up.
2) Declare all of your columns NOT NULL unless you need to store NULL values (NULL is not the same as 0 or the empty string). If you need to store NULL, you'll know. Again, NOT NULL saves space and speeds things up.
3) Making INT-family columns UNSIGNED will effectively double the positive range with the same storage requirement. For example, TINYINT's highest value is 127. TINYINT UNSIGNED's highest value is 255.
Indexes and optimization
1) First, I sometimes see people create tables like this:
CREATE TABLE table ( |
2) Indexes/keys are the key (no pun intended) to fast queries (especially joins). The 3 types of indexes are PRIMARY, UNIQUE, and KEY/INDEX (same thing). It's hard to explain when and how to use indexes, so I'll just point you to some relevant pages in the MySQL manual: Speed of SELECT queries and How MySQL uses indexes.
3) When you have your indexes setup, use EXPLAIN on SELECT queries to make sure your indexes are actually being used.
User login
Follow Us
Who's online
There are currently 0 users and 1 guest online.
Who's new
- Nisha
- linnaeus
- Yameen
- TalleyReedy
- admin
