June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

… 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…

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

…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… …

Post: Hidden columns of query_review_history table

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

…. 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 columncolumn MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL

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

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…

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

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

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

MySQL data sets and queries. Another advantage is that it works with all MySQLtable `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 …

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

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

Post: Using Flexviews - part two, change data capture

…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