June 20, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…a random value, look up the row with “=” instead of “>” and be guaranteed to find exactly one match.  But the…in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order.  This made the first table in the…

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

…table name in form database_name/table_name e.g. sakila/actor. `ID` is a table identifier. We will need the table id to find indexes of the table. mysql> select * from SYS_TABLES…

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

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

…how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the…tables in INFORMATION_SCHEMA that can help us to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM…

Filed Under: Insight for DBAs, MySQL

Post: A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By 8 Comments

…ENGINE=MyISAM/g’ schema.sql root@domU:/mnt/tables# cat schema.sql | mysql -u root test The MyISAM table recording the space ids of the tables mysql> show create table…the table with table names and space ids The challenge was now to fill up the table. It is fairly easy to find the space id in a…

Filed Under: MySQL

Post: How to Monitor MySQL with Percona's Nagios Plugins

February 22, 2012 By 11 Comments

…to find the right threshold for all situations. This suggestion is a consequence of the previous two, but it’s worth calling out separately in…MySQL, you won’t have any problems until you try to create or drop a table, or the database itself. Likewise, if a system administrator leaves a…

Filed Under: Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

…in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in the sense that the SQL required to run a…tables created in 5.5.30 where, of course, the only difference is that the engine is MyISAM rather than InnoDB. Loading the…

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

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

March 4, 2013 By 7 Comments

…a table, and configure that table to be our stopword list for InnoDB? This is the table that we’re trying to emulate: mysql: SHOW CREATE TABLE…to an InnoDB table, be prepared for a table rebuild. Calculation of match score is completely different between the two engines; sometimes this leads to…

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

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

…to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine…MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us to find the cause in…

Filed Under: Insight for DBAs, MySQL

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…to find out and to avoid them. How? Just specify the index name and MySQL will avoid to create duplicate indexes: mysql> alter table…The redundant index is on the ‘name’ column. To take benefit from a composite index MySQL doesn’t need to use all the columns of that index, the…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…in set (0.03 sec) Now I need a way to find the set of indexes in all_indexes, but not in…

Filed Under: Insight for DBAs, MySQL, Percona Software
« Older Posts