This blog post is part two in what is now a continuing series on the Star … using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE… innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… plan: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: index_merge possible_keys: PRIMARY,parent_id,status,user_type key… was the most commonly executed query on the server by two orders of magnitude. Other queries were running maybe 1000 or…
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… sec) In this case there are obviously two rows with value 1 in merge table however select querying by primary index returns only… NULL for the merge table: “create table tx (id int unsigned, key(id)) type=merge union(t1,t2)” If you create table this way it… underlying MyISAM tables, but not in the MERGE table. There it is indexed but not as a PRIMARY KEY because a MERGE table cannot enforce…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 2918; RW-excl spins 3463, OS waits 3163 There are two portions in this section. One is list of current waits… len 887, seg size 889, 2431891 inserts, 2672643 merged recs, 1059730 merges Hash table size 8850487, used cells 2381348, node heap has 4091… buffer, how many recs were merged and how many merges did it took. Ratio of number of merges to number of inserts is…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… dump and load a copy of that table into your working index_analysis schema. Merging stats from several servers In the case… those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
… code base to be merged as it is quite the same task in terms of query optimization. Derived Tables are still handled… in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example). One… query merge is impossible or if requested by view creator. What does it mean in terms of performance: Query on base table…
Comment: Why MySQL could be slow with large tables ?
January 7, 2009 By 141 Comments
… a project containing 5 tables and a realtime search (AJAX). First I split the searchable data into two tables and did a LEFT JOIN to get the results. I noticed that when there were few million records in both table, things got extremely sluggish. Then I merged the two tables and tested it with 35 million…
Comment: Merge Tables Gotcha
September 2, 2009 By 4 Comments
… similar happened to me) : Check to see that all five tables have the same indexes as well as the same types… the merge on tables T1 + T2 + T3 + T4 + T5 failed then we removed one table at a time, then constructed the merge table with two tables at…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…we’ll see the following picture: Comparing these two graphs we can see few different observations: …bad with sequential IO, which is what file merge process uses. This is important illustration as …’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… a way) – MySQL can’t do hash join or sort merge join – it only can do nested loops method which requires… the join. Some people assume join would be close to two full table scans (as 60mil of rows need to be read… data you’re going to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets…
