by Andrew Aksyonoff | Jan 10, 2008 | Insight for Developers
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...
by Andrew Aksyonoff | Dec 20, 2007 | Insight for Developers
mysql_connect() function in PHP’s MySQL interface (which for reference maps to mysql_real_connect() function in MySQL C API) has a $client_flags parameter since PHP 4.3.0. This parameter is barely known and almost always overlooked but in some cases it could...
by Andrew Aksyonoff | Dec 18, 2007 | Insight for Developers
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...
by Peter Zaitsev | Dec 9, 2007 | Insight for DBAs
If you enable logging of all queries as “slow queries” using the patch or MySQL 5.1 you can get log file to grow huge. Same may happen with general log file. In some cases we’ve got log file sizes of 100G or more which may need to be cleaned up. Here...
by Aurimas Mikalauskas | Oct 29, 2007 | Insight for DBAs
Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE huge_table CHANGE id id int(6) NOT NULL and then wait hours for table rebuild to complete. If you’re unlucky i.e. you have a lot...
by Aurimas Mikalauskas | Oct 16, 2007 | Insight for Developers
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...
by Peter Zaitsev | Sep 13, 2007 | Insight for DBAs
General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can’t switch it on and off without restarting server until MySQL 5.0. What can you do in MySQL 5.0 and below ? Use Our Patch – With this...
by Vadim Tkachenko | Sep 13, 2007 | Insight for DBAs
If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue. What I usually do to fast compile and test is ./configure --prefix=/dir/to/mysql make make install 123 ./configure...
by Alexey Kovyrin | Aug 28, 2007 | Benchmarks, Insight for Developers
When we optimize clients’ SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one – to get a result set, another – to count total number...
by Peter Zaitsev | Aug 18, 2007 | Benchmarks, Insight for DBAs
I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice. I tested full table scan of the table...
by Peter Zaitsev | Aug 12, 2007 | Insight for DBAs
I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries – to keep queries simple without really thinking how it affects server performance. Even worse...
by Peter Zaitsev | Aug 12, 2007 | Percona Events
Yesterday I ran into the article which sheds some light on FaceBook search implementation. As we’re recently a lot into search having implemented a bunch of search projects ourselves and helped number a of customers with their full text search needs I decided to...
by Vadim Tkachenko | Aug 6, 2007 | Insight for Developers
Recently we were puzzled by question how query_cache works with column level privileges. The question was appeared as we discovered function query_cache_send_result_to_client is called before real parsing of query, so at the moment of execution the query_cache is not...
by Peter Zaitsev | Jul 24, 2007 | Insight for DBAs
Looking for documentation for read_rnd_buffer_size you would find descriptions such as “The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance” which is...
by Peter Zaitsev | Jul 8, 2007 | Insight for DBAs
In my previous post I mentioned you might need to increase net_write_timeout to avoid connection being aborted and now I think I should have better explained that. MySQL uses a lot of different timeout variables at different stages. For example when connection is just...
by Peter Zaitsev | Jul 5, 2007 | Insight for Developers
What does working with large data sets in mySQL teach you ? Of course you have to learn a lot about query optimization, art of building summary tables and tricks of executing queries exactly as you want. I already wrote about development and configuration side of the...
by Peter Zaitsev | Jul 1, 2007 | Insight for Developers
On many web sites you would see a counter how many time given object – blog post, forum thread, image, movie etc was viewed. This is sometimes handy feature but it can be rather expensive from performance point of view. The nasty thing with counters as they are...
by Peter Zaitsev | Jun 18, 2007 | Insight for Developers
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...
by Peter Zaitsev | Jun 16, 2007 | Insight for Developers
Just found this little handy feature today: mysql> insert into c select rand()*1000, sha1(rand()) from c; Query aborted by Ctrl+C ERROR 1317 (70100): Query execution was interrupted 123 mysql> insert into c select rand()*1000, sha1(rand()) from c;Query aborted...
by Peter Zaitsev | Jun 12, 2007 | Insight for Developers
To be honest I’m not a big fan of Stored Procedures, at least not in the form they are currently implemented in MySQL 5.0 Only SQL as a Language Which is ancient ugly for algorithmic programming and slow. It is also forces you to use a lot of foreign constructs...
