June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

… it returns a better random choice. Another workaround may be to add a column to the table, and populate the rows you want to… the tradeoff of this solution is that it requires storing another column, and reinitializing the sequence after making certain insert/update/delete… the Percona Live MySQL Conference and Expo in Santa Clara, California starting March 31, 2014. Watch more webinars from Percona in the…

Filed Under: Insight for Developers, MySQL, MySQL Webinars Tagged With: , , ,

Post: Understanding the maximum number of columns in a MySQL table

April 8, 2013 By 5 Comments

…RETURN(HA_ERR_TO_BIG_ROW); } Which is curiously gone from MySQL 5.6, it’s replaced by the following set …(REC_MAX_N_FIELDS – DATA_N_SYS_COLS * 2) Which adds up to (1024-1)-3*2=1017 which is …folks, in MySQL 5.6 you can create a table with a few more columns in InnoDB! This led me on another idea… …

Filed Under: MySQL Tagged With: , , , , , , , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… > select * from test where id=1; +—-+——+ | id | x | +—-+——+ | 1 | foo | +—-+——+ 1 row in set (0.00 sec) session2 > ALTER TABLE test add column c… was using MySQL version < 5.5 as the datastore. There was another interesting case this time with how Active MQ uses MySQL when…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , , ,

Post: Hidden columns of query_review_history table

August 28, 2012 By Leave a Comment

…add and have the following useful columns populated in query_review_history table…from the another server. Having hostname set might be useful for filtering queries in the database aggregating slow queries from…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…. Q: ORDER By optimization issues: select * from table where A=xxx and B between … column and using order by on another column. do i need to add the index on column …column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…

Filed Under: Insight for DBAs, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…table: SELECT non_key_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps, without MRR: SELECT key_column…>= ’1993-08-01′ and o_orderdate < date_add( ’1993-08-01′ ,interval ’3′ month) and l…times between MySQL 5.5 and MySQL 5.6/MariaDB 5.5 This is another area of…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…another…mysql> show warnings; +———+——+————————————————–+ | Level | Code | Message | +———+——+————————————————–+ | Warning | 124 | InnoDB rebuilding table to add column…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

… MySQL data sets and queries. Another advantage is that it works with all MySQL…table `ontime_fact` is created by joining the newly constructed dimension table tables to the staging tables, omitting the dimension columns from… when Shard-Query adds parallelism when “subqueries in the from clause” are …

Filed Under: MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table…from a monthly summary. The monthly summary is actually another…column `mview$pk` which is an auto_increment BIGINT surrogate key for the table…

Filed Under: Insight for DBAs, MySQL

Post: Using Flexviews - part two, change data capture

March 25, 2011 By 26 Comments

…SBR changes from a MySQL master. I’ll talk more about that in another blog …` metadata table, and it creates the changelog table itself. $ php add_table.php –schema=test –table=demo…table only has two columns, but the changelog contains five. All change logs contain three additional metadata columns…

Filed Under: Insight for DBAs, Insight for Developers, MySQL
« Older Posts