Sometimes you need to work with big numbers in PHP (gulp). For example, sometimes 32-bit identifiers are not enough and you have to use BIGINT 64-bit ids; e.g. if you are encoding additional information like the server ID into high bits of the ID. I had already written about the mess that 64-bit integers are [...]
Fixing column encoding mess in MySQL
December 18, 2007 By 32 Comments
Just had an interesting issue with an encoding mess on a column containing non-ASCII (Russian) text. The solution was not immediately obvious so I decided it’s worth sharing. The column (actually the whole table) was created with DEFAULT CHARSET cp1251. Most of the data was in proper cp1251 national encoding indeed. However, because of web [...]
Be careful when joining on CONCAT
October 16, 2007 By 15 Comments
The other day I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT(‘prefix-’, tb2.id) with tb1.vid – indexed varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use key, forced a different join order, it did not want [...]
Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
I’ve already wrote a few times about various projects using Sphinx with MySQL for scalable Full Text Search applications. For example on BoardReader we’re using this combination to build search against over 1 billion of forum posts totaling over 1.5TB of data handling hundreds of thousands of search queries per day. The count of forum [...]
Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
I prefer to use Integers for joins whenever possible and today I worked with client which used character keys, in my opinion without a big need. I told them this is suboptimal but was challenged with rightful question about the difference. I did not know so I decided to benchmark. The results below are for [...]
MySQL Error control changes
February 19, 2007 By 4 Comments
In MySQL 5.0 mainly error control was improved, such as strict mode was added to change famous MySQL behavior of cutting too large strings, too big numbers and allowing you to use dates such as February 31st. In one case however reverse change was done – in regards to storage engine initialization. Previously if you [...]
Why Index could refuse to work ?
September 8, 2006 By 15 Comments
Have you ever seen index which refused to be used even if there is every reason for it to work (from the glance view):
1 2 3 4 5 6 7 | mysql> explain select * from article where article_id=10; +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | article | ALL | PRIMARY | NULL | NULL | NULL | 93490 | Using where | +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec) |
Why on the earth index would not be used you would think, even if MySQL is mentioning it in “possible keys” ? Should you try to force it ?
MySQL Prepared Statements
August 2, 2006 By 21 Comments
If you care about archiving best performance in your application using MySQL you should learn about prepared statements. These do not neccesary provide performance beneft but they may, they also have other benefits. As a quick introduction – before MySQL 4.1 there were only textual statements and textual protocol for data transfer – query was [...]
Handling big result sets
June 26, 2006 By 13 Comments
Sometime it is needed to handle a lot of rows on client side. Usual way is send query via mysql_query and than handle the result in loop mysql_fetch_array (here I use PHP functions but they are common or similar for all APIs, including C). Consider table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | CREATE TABLE `longf` ( `f1` int(11) NOT NULL auto_increment, `f2` date default NULL, `f3` date default NULL, `f4` varchar(14) default NULL, `f5` varchar(6) default NULL, `f6` date default NULL, `f7` smallint(6) default NULL, `f8` smallint(6) default NULL, `f9` varchar(13) default NULL, `f10` varchar(39) default NULL, `f11` int(11) default NULL, `f12` float default NULL, `f13` int(11) default NULL, `f14` smallint(6) default NULL, `f15` varchar(39) default NULL, `f16` date default NULL, `f17` smallint(6) default NULL, `f18` int(11) NOT NULL, `f19` date default NULL, `f20` date default NULL, `f21` varchar(14) default NULL, `f22` varchar(6) default NULL, `f23` date default NULL, `f24` smallint(6) default NULL, `f25` smallint(6) default NULL, `f26` varchar(13) default NULL, `f27` varchar(39) default NULL, `f28` int(11) default NULL, `f29` float default NULL, `f30` int(11) default NULL, `f31` smallint(6) default NULL, `f32` varchar(39) default NULL, `f33` date default NULL, `f34` smallint(6) default NULL, `f35` int(11) NOT NULL, PRIMARY KEY (`f1`) ); select count(*) from longf; +----------+ | count(*) | +----------+ | 5242880 | +----------+ |
INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 48 Comments
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 freqent task while keeping it beautiful [...]
