June 18, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

… just running the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the… against multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in the index.  Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot…

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

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

…(`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `NAME` is a human readable table name in form database_name/table_name … me know. mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; +———-+—–+————-+——-+———+——+——+ | TABLE_ID | POS | NAME | MTYPE | PRTYPE | LEN | PREC…

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

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

…SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS `’, P.TABLE_SCHEMA, ‘.’, P.TABLE_NAME, ‘.’, P.COLUMN_NAME…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… need to create an *InnoDB* table with a single VARCHAR column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb… stopword tables, it simply mentions the table engine and the column name/type, so I’m not sure if this is an… in the search results. But does it? With 5.5: mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST (‘+james +peterson +arizona’ IN BOOLEAN…

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

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

February 26, 2013 By 1 Comment

… in my table or column names to begin with (I may be one of the few people that likes lower_case_table_names = 1 in… | 0 | +—————————+——–+ 10 rows in set (0.00 sec) mysql> insert into dir_test_innodb (full_name, details) SELECT reverse(full_name), details FROM dir…

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

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

March 21, 2012 By 10 Comments

… following: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer… query used is: select c_custkey, c_name, sum(l_extendedprice * (1 – l_discount)) as revenue, c_acctbal, n_name, c_address, c…

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

Comment: Duplicate indexes and redundant indexes

January 19, 2012 By 38 Comments

… in a foreign key constraint +++Query++++ SELECT a.table_schema, a.table_name, a.constraint_name, a.constraint_type, convert(group_concat(DISTINCT b.column_name…, a.table_name, a.constraint_name, a.constraint_type, b.referenced_table_name, b.referenced_column_name UNION SELECT table_schema, table_name, index_name as constraint_name, if…

Filed Under: Insight for DBAs Tagged With:

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…in set (0.00 sec) session6 > ALTER TABLE test_2 DROP COLUMN c; session7 > select * from test_2 order by id; session8 > select… | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 0 | +—————+——-+ 2 rows in set (0.00 sec) session10 > select …

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

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

… used then, after the selected rows are read from table1, the values of indexed columns that will be used to… are read and hash function is applied to the columns participating in the join condition and then a hash table…’s take a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6…

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

Post: Analyzing Slow Query Table in MySQL 5.6

February 18, 2013 By 1 Comment

…_buff, “%.6f”, ulonglong2double(lock_utime)/1000000.0); However, just like in prior releases of MySQL, when writing slow query information to… of script in the past, but here’s mine: cat <<'GO' | mysql --raw --skip-column-names –quick –silent –no-auto-rehash –compress $* SELECT CONCAT…

Filed Under: Events and Announcements, Insight for DBAs, Live Virtual Training, MySQL, Percona Toolkit, Training Tagged With: , , , , , , ,
« Older Posts