I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]
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 [...]
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) |
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 [...]
