… store hierarchies according to textbook solutions. CREATE TABLE TreePaths ( ancestor CHAR(1) NOT NULL, … AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The…in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… affected (0.00 sec) mysql> create table am(i int unsigned not null auto_increment primary key) type=merge union(a1,a2) insert_method… you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment=1000; Query…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…in the presentation to convert sort to the union for small ranges. Q: In the case of a junction table…, which is 64 per table in recent MySQL versions. However it is…creating an index in every table you create? Example: CREATE TABLE user_competition_entry user_id (INT), competition_id(INT); The table…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows in…| 6 | 0.0001336667 | | Creating tmp table | 0.000548 | 0.32 | 1 | 0.0005480000 | | Opening tables | 0.000196 | 0.11 …. If there were, I could add in a UNION to inject another row for “lost…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
…performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my… difference in execution speed comes from the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… well as IN lists make this optimization impossible, not even speaking about index merge optimization. Lets look at this example: CREATE TABLE `utest… the fact MySQL is unable to handle even basic UNION with limit (without order by) optimally – in creates result set for the union fully and…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…mysql> create table…UNION DISTINCT of 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: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… there are less than 200 tables in this system. innodb_stats_on_metadata is disabled by default in MySQL 5.6 Welcome to much… when you have large number of tables or if you do many create/drop of innodb tables. optimizer_switch is the catch all…_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql…
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
… rows affected (0.02 sec) mysql> create table tm(id int unsigned not null primary key) type=merge union(t1,t2); Query OK, 0… on the given column instead: mysql> create table tx (id int unsigned not null, key(id)) type=merge union(t1,t2); Query OK, 0… merge table: “create table tx (id int unsigned, key(id)) type=merge union(t1,t2)” If you create table this way it structure will be in fact…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…single database table called ontime_stage. Then, the airport information was extracted: create table dim_…in the fact table, respectively. There are nearly 400 major airports included in the data set. mysql…are in used in the query: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION …
