Infobright and InnoDB AMI images are now available There are now demonstration AMI images for Shard-Query. Each image comes pre-loaded with the data used in the previous Shard-Query blog post. The data in the each image is split into 20 “shards”. This blog post will refer to an EC2 instances as a node from here [...]
Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
Accidentally me and Baron played with InfoBright (see http://www.mysqlperformanceblog.com/2009/09/29/quick-comparison-of-myisam-infobright-and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB. Reading comments to Baron’s post I tied to load the same data to LucidDB, but I was not successful in this. I tried to analyze a bigger dataset and I took public [...]
How number of columns affects performance ?
September 28, 2009 By 11 Comments
It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row [...]
Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
If you ever wondered how big is that or another index in InnoDB … you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR – since you need to estimate average length) on count of records. And it still would be quite [...]
PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
Quite common task during schema review is to find the optimal data type for the column value – for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain any NULLs or it can be defined NOT NULL which reduces space [...]
High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work. The first thing these have in common is that they’re generally some kind of loggable event. The next characteristic of a lot of these systems (real or planned) is [...]
Picking datatype for STATUS fields
August 9, 2008 By 24 Comments
Quite commonly in the applications you would need to use some kind of “status” field – status of order – “new”, “confirmed”, “in production”, “shipped” status of job, message etc. People use variety of ways to handle them often without giving enough thought to the choice which can cause problems later. Perhaps worst, though quite [...]
Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible. For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:
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 | CREATE TABLE `bbool` ( `b1` bit(1) NOT NULL, `b2` bit(1) NOT NULL, `b3` bit(1) NOT NULL, `b4` bit(1) NOT NULL, `b5` bit(1) NOT NULL, `b6` bit(1) NOT NULL, `b7` bit(1) NOT NULL, `b8` bit(1) NOT NULL, `b9` bit(1) NOT NULL, `b10` bit(1) NOT NULL ) ENGINE=MyISAM mysql> show table status like 'bbool' \G *************************** 1. row *************************** Name: bbool Engine: MyISAM Version: 10 Row_format: Fixed Rows: 10 Avg_row_length: 7 Data_length: 70 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 00:41:01 Update_time: 2008-04-24 00:45:40 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) |
MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
Few days ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So changing query to:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +----------------------------------+ | name | +----------------------------------+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +----------------------------------+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | people | range | age | age | 4 | NULL | 9 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) |
So as you see there are really different types of ranges in MySQL. IN range allows [...]
