notes on mysql while reading ... (3)

High Performance MySQL

Indexes

...
We’re not just being picky: these two kinds of index accesses perform differently. The
range condition (i.e. BETWEEN, >, <)makes MySQL ignore any further columns in the index, but the multiple equality condition (i.e. IN) doesn’t have that limitation.


repairing

CHECK TABLE
REPAIR TABLE
myisamchk

Updating Index Statistics
API calls: records_in_range( ) and info( )
MySQL’s optimizer is cost-based
ANALYZE TABLE
Each storage engine implements index statistics differently
MyISAM stores statistics on disk, and ANALYZE TABLE performs a full index scan to compute cardinality. The entire table is locked during this process.
InnoDB does not store statistics on disk, but rather estimates them with random index dives the first time a table is opened. -> less accurate statistics, no blocking

You can examine the cardinality of your indexes with the SHOW INDEX FROM command -> Cardinality; or by using INFORMATION_SCHEMA.STATISTICS

Fragmentation - Row fragmentation & Intra-row fragmentation
MyISAM tables may suffer from both types of fragmentation, but InnoDB never fragments
short rows. To defragment data, you can either run OPTIMIZE TABLE or dump and reload the data.

hm they recommend using of summary table instead of counting of the records of the real table

MySQL’s ALTER TABLE performance can become a problem with very large tables. ALTER TABLE lets you modify columns with ALTER COLUMN, MODIFY COLUMN, and CHANGE COLUMN. All three do different things. MODIFY COLUMN always cause table rebuilds.

Building MyISAM Indexes Quickly - ALTER TABLE test.load_data DISABLE KEYS; -- load the data ALTER TABLE test.load_data ENABLE KEYS;
! Unfortunately, it doesn’t work for unique indexes, because DISABLE KEYS applies only to nonunique indexes

The InnoDB Storage Engine
Clustering by primary key: All InnoDB tables are clustered by the primary key, which you can use to your advantage in schema design.
No cached COUNT(*) value: Unlike MyISAM or Memory tables, InnoDB tables don’t store the number of rows in the table, which means COUNT(*) queries without a WHERE clause can’t be
optimized away and require full table or index scans.