… my complete answers: Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. … a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the …. It was more efficient in this case to force MySQL to scan the `title` table first, grouping by …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…’s tests Percona Server scalability is somewhere in between of MySQL 5.5 and MySQL 5.6. In order to understand more about… indices to the buffer pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i… –forced-shutdown=1 –max-time=120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
…use for all of the benchmarks on both systems. The benchmark tool used was sysbench 0.5 and the version of MySQL used…forced-shutdown=1 –max-time=7200 –max-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql…= 64M serverid = 101 key_buffer_size = 8M …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…the form of deadlock errors. (This is actually a form of Eventual Consistency where the client is forced …. Our unit of locking in Innodb is a single row (well, the PRIMARY KEY index entry …tradeoffs. Most of us are used to the tradeoffs we take when deploying conventional stand-alone MySQL Innodb …
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… as KEY `group_id` (`group_id`,`parent_id`,`published`) As you can see MySQL selects to use “ref” access only using two first key parts… of its side effects FORCE INDEX actually forces index to be used to largest extent possible: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 force…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution … in Tokyo tyrant. All to often people force themselves into using a database like MySQL with no thought into whether if its the… a ton of work for clients who use their database like most people use memcached . Lookup a row based on a key, update the…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… table scans access the table by primary key. This forces the storage engine to touch the … secondary indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_…key_len: 4 ref: ssb.dim_date.D_DateKey rows: 2837 Extra: Using where 2 rows in set (0.00 sec) mysql…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to…used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem FORCE…IO bound. For the purpose of benchmarking IO bound workload, …
Post: How Does Semisynchronous MySQL Replication Work?
January 19, 2012 By 24 Comments
…successfully themselves (duplicate key error, anyone?). If any of these problems happens… connection is forced to wait until at least one of the replicas…synchronous replication is not useful. It is useful, but if you misunderstand… to read the relevant section of the MySQL manual. But read carefully, …
