… here are all the questions with my complete answers: Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e… columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join, can you… I did not use the STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index-scan of 7 rows…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…and adaptive hash index are cold for the cold test. All tests were done with no concurrency. …indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used…_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage_engine=…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…is a single row (well, the PRIMARY KEY index entry for that row). This means typically …with some metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1); UPDATE groups SET last_joined…application needs to be modified. Generally if you use Innodb, you should be able to handle …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10, sbtest$I_r100, sbtest$I_r1000, with …_io_threads = 4 innodb_io_capacity = 4000 innodb_use_native_aio=0 #not innodb options (fixed) port = … max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_nationkey | i_s_nationkey | 5 | const | 808 | 100.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i… – Join a small table with a large table with a WHERE clause on a non-indexed column The SQL used for this test together with its…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… is being used then, after the selected rows are read from table1, the values of indexed columns that will be used to perform… the columns of table t1 that are two be used to join rows with table t2. This step is called the build step… 0.16s less as compared to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… should be concerned if you’re dealing with very large data sets are Buffers, Indexes and Joins. Buffers First thing you need to… benefit from using indexes. In fact even MySQL optimizer currently does not take it into account. For In memory workload index accesses might… require radom IO if index ranges are scanned. There are also clustered keys in Innodb which combine index access with data access, saving…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…. The query with BETWEEN range replaced with IN list was instant 0.00 sec same as the query using join with day list…NULL rows: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: …
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… to paint it.” — Dr. Emmett Brown I populated these tables with enough data to serve our purpose. Our hypothetical sales query…: 1306 Extra: Using where; Using index In this case MySQL does not print ref at all, because there is no join, however you can… rows: 206494 Extra: Using where; Using index This query uses 7 bytes of `itm_prd_id__and__itm_order_date` index – 4 bytes is…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…schema with one command: mysql> create table index_analysis.used_indexes select * from information_schema.index_statistics;…indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX…
