Indexes in MySQL

MySQL does not always make a right decision about indexes usage.
Condsider a simple table:

Shell

CREATE TABLE `t2` (
`ID` int(11) default NULL,
`ID1` int(11) default NULL,
`SUBNAME` varchar(32) default NULL,
KEY `ID1` (`ID1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

123456

CREATE TABLE `t2` (`ID` int(11) default NULL,`ID1` int(11) default NULL,`SUBNAME` varchar(32) default NULL,KEY `ID1` (`ID1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1

Shell

SELECT COUNT(*) FROM t2

1

SELECT COUNT(*) FROM t2

;
250001 […]

Read more

InnoDB memory usage

There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup.
Some important constants:
NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384
OS_THREADS= if (innodb_buffer_pool_size >= 1000Mb) = 50000
else if (innodb_buffer_pool_size >= 8Mb) = 10000
else = 1000 (it’s true for *nixes, for Windows there is a bit […]

Read more

MySQL INSERT ON DUPLICATE KEY UPDATE and counters

MySQL INSERT ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but I still constantly see people unaware of it.
Myself I like this feature big deal because it is designed in truly MySQL style – very efficient solution for frequent task while keeping it beautiful […]

Read more