- Learn from smarties
- Evangelize good DB design (which in turn helps you solidify your own knowledge)
- Talk to business people
- Benchmark your queries to determine the fastest
Use InnoDB almost always, but there are a few unique situations where MyISAM is better: logging and read-only.
- InnoDB
- Transactional
- Hot (Online) Backup
- Crash Safe..ish
- MyISAM
- Full text indexing
Use unsigned numbers whenever you can. It gives you more positive numbers and it saves MySQL from having to check the sign every time.
VARCHAR vs CHAR. Use CHAR when you can. It should be used when your data will have a fixed length (exactly 2 for state codes for instance). It is significantly faster. A good use would be state codes, password hashes, etc.
Use TIMESTAMP instead of DATETIME.
TIMESTAMP is the number of seconds since the EPOCH date vs DATETIME which is like 2014-12-01 08:12:21.
Normalization is like having joins vs having columns like tag1, tag2, tag3...
When using TEXT, you can sort with SUBSTRING() in your query.
Avoid NULL whenever possible. It is not good for comparison.
ENUM says that I can't store anything in the column besides what is in the ENUM. It restricts column values to a list. I.E. you might have an ENUM of state codes on an order shipping_state column.
Don't use 0000-00-00 00:00:00 as a default/null DATETIME value. Use the date you started the project instead (for instance) as a default date. This is assuming that no records will have dates except those after your start date. But, that brakes for some apps right? Like if you store birthdays.
You can index things using a hash.
| id | url | hash |
|---|---|---|
| 1 | http://google.com | 7hd8h73hs8922j8hd78hq |
| 2 | http://youtube.com | 8js2j9sj292jhjdhsh288 |
SELECT url FROM websites WHERE hash = CRC32('http://google.com') AND url = 'http://google.com';
You SELECT using the hash first which narrows down to 1 or a few results due to hash collisions.
This method can cause issues between 32 and 64 bit machines or production and development.
When you have a compound index/key, KEY(email,password), you should list the one first which returns less rows fi
| id | name | sex | country |
|---|---|---|---|
| 1 | John | m | US |
| 2 | Bill | m | US |
| 3 | Susan | f | US |
KEY(sex,country)
KEY(country)IN like below:
SELECT name, sex, country FROM profiles WHERE country = ? AND IN('m','f')
We can use the KEY(sex,country) key if we say IN('m','f').
Don't get redundant data.
SELECT name, img FROM comments WHERE user_id = 123 LIMIT 1;
SELECT comment FROM comments WHERE user_id = 123;
On large changes, drop all your indexes then reapply them. Or:
Copy a table's structure w/o indexes: orders_new SELECT * FROM orders and insert into orders DROP orders change orders_new to orders
It's much faster than trying to add a new index (on a table with a very large amount of records).
Don't SELECT *. It's faster if you SELECT a, b, c because MySQL don't have to lookup the table columns.