…: Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it’s… primary key solution for random selection only work when the IDs for movies are distributed uniformly over the range 1..MAX… it created a temporary table to count the movies per production year for each kind_id. It was more efficient in this…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… course I wanted to compare InnoDB performance vs TokuDB. I have a particular workload I… and transactions for this workload are: $ID=monotonically increasing ID $K=rand(0,10000) // distributed by …_log = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…. This is, however, only a hypothesis. vCache vs. FlashCache – dirty page threshold Finally, we examine…32 –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand-init=… thread_stack = 192K tmp_table_size = 64M serverid = 101 key_buffer_size = 8M read_buffer_size…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… a results comparison on a single-node (on the left) vs. two-node (on the right) configuration. No surprises here, the… pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like… sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
…=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582 | +————+ 1 row in set (1.17 sec) mysql> select count(val2) from fact; +————-+ | count(val2…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… NOT NULL default ’0′, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `country_id` (`country_id`,`state_id`,`city`) ) In this benchmark we used… in Part 2). There MyISAM shows bad scalability with increasing count of thread. I think the reason is pread system call….85 times. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id between %d and %d MyISAM scales good here, because…
Comment: COUNT(*) for Innodb Tables
December 20, 2011 By 32 Comments
… +—————+————–+——+—–+———+—————-+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | id_acct | int(11) | NO | MUL | 0 | | mysql> select count(id_acct) from awm_messages; +—————-+ | count(id_…’s due to the difference in field type (bigint vs int…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…` decimal(10,2) NOT NULL, KEY `cat_id` (`cat_id`,`price`), KEY `cat_id_2` (`cat_id`,`seller_id` ) mysql> explain select * from goods where…: SIMPLE table: goods type: ref possible_keys: cat_id,cat_id_2 key: cat_id_2 key_len: 8 ref: const,const rows… hint. The other problem you may have however is calculating count of matching rows which may be even trickier to slow…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…caption id=”attachment_13599″ align=”alignright” width=”210″] MySQL 5.6 vs MySQL 5.5 & the Star Schema Benchmark[/caption] So far…_pool from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number…_pool from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… tested only READ queries, similar to ones in benchmark InnoDB vs MyISAM vs Falcon (http://www.mysqlperformanceblog.com/2007/01/08/innodb… NOT NULL default ’0′, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), KEY `country_id` (`country_id`,`state_id`,`city`) ) In this benchmark we used… access to data. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id between %d and %d The same comment as…
