What should I know about designing databases for scalability?

Question

Apart from creating indexes on columns that are retrieved often, and doing SELECT col1, col2, col3 FROM tbl instead of SELECT * FROM table, what else should I know about designing databases for scalability / fast performance?

Answer 1

There is tons of stuff, you need to know, small sample

When to use partitioning
make tables as narrow as possible so that you can fit more rows per page
how to minimize locking and blocking
sharding
disk IO subsystem (raid 5 or 10, SAN or HDD)
making queries SARGable so that indexes will be used
Explain plan
Using the correct data types
Difference between a seek and a scan

Answer 2

You might find this thread interesting:

Database development mistakes made by application developers

Answer 3

You should know:

1. how order affects JOINs.
2. how to use EXPLAIN PLAN.
3. how certain kinds of WHERE clauses cannot use indexes and force table scans.

Answer 4

• Learn how to use explain to understand the way MySQL is processing your query
• Learn how to use Slow Query Logging
• Think about partitioning/sharding
• Consider the database engine that you're using