June 18, 2013

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

…the same table in a single query in MySQL. But we can use MySQL’s multi-table DELETE syntax, to find…of itself. I also wrote about Closure Table in my book, SQL Antipatterns: Avoiding the Pitfalls of Database…

Filed Under: Insight for Developers, MySQL

Post: Analyzing Slow Query Table in MySQL 5.6

February 18, 2013 By 1 Comment

…the best tool for evaluating where your database response time is being spent. This month we saw the GA release of…in prior releases of MySQL, when writing slow query information to the log table, query times and lock times are truncated to to integers: sql…FIND_IN_SET(sql…

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

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

February 26, 2013 By 1 Comment

…the data, the first thing we notice is that there are a lot of “new” InnoDB tablespace files in our database…the FT indexes on that table by querying the various I_S.INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table…

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

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

…are no orphaned rows. But there are probably hundreds of foreign key relationships in any complex database.  We can find all foreign keys by querying the… table with similar structure, and track our database design manually, so we can use the same SQL queries for quality control: mysql> CREATE TABLE…

Filed Under: Insight for DBAs, MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…of values for various attributes, for example; or the presence or absence of values.  You can put these into the database in a little meta-table…the clicks column record the total, and the blue_clicks column record only blue clicks; to find…

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

Post: Quick comparison of MyISAM, Infobright, and MonetDB

September 29, 2009 By 30 Comments

…of the box, for all three databases. The first thing I tried doing was loading the data with SQL…the mailing lists to find the correct COPY syntax — it wasn’t that listed in the…

Filed Under: MySQL Tagged With: , , ,

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…in our schemas can hurt the performance of our database: They make the optimizer phase slower because MySQL needs to examine more query plans. The…find the three different types of keys explained before. Lets try it: mysql> show create table…

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

Post: Data mart or data warehouse?

July 15, 2010 By 14 Comments

…the kind of SQL which a ROLAP tool must generate to answer those queries. Performance challenges with larger databases…tables in the warehouse schema example would be considered a snowflake. A dimension table (item) must be joined to additional tables (item_category,category) to find the…

Filed Under: Insight for Developers, MySQL

Post: Timezone and pt-table-checksum

September 25, 2012 By 1 Comment

…the checksum table to find the chunks but still recalculates the checksums on the fly to get the actual rows in need of syncing, it will not find…

Filed Under: Insight for DBAs, MySQL, Percona Software

Comment: Spreading .ibd files across multiple disks; the optimization that isn't

December 26, 2010 By 23 Comments

…the table in the other database. For example: –This database is located on RAID10 and that we already have tables in…of tables in the_db, transactional tables and log tables, and the application thinks the log tables are in `the_db`. We know that they are actually in…

Filed Under: Insight for DBAs, MySQL Tagged With: , , , , , ,
« Older Posts