June 19, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… between tables versus observing queries only.  An additional benefit …sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127…., number of children, parents, count of columns, row counts, and comments — a … and can then write into your metadata file. Donate – …

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

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

…but there are generally other considerations to take into account. He also didn’t answer the ‘Insert’… do queries on any other column, these could easily be quicker on a partitioned table, as Mysql can … quicker than deleting a few hundred thousand rows… for example, if you need to delete. However…

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

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL…Query OK, 0 rows affected (0.00 sec) session2 > ALTER TABLE test add column c char(32) default ‘dummy_text’; Query OK, 2 rows…

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

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

April 4, 2011 By 20 Comments

….schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT” statements from …Note that every MV gets a special column `mview$pk` which is an auto…Query OK, 0 rows affected (42 min 42.14 sec) mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query OK, 0 rows…

Filed Under: Insight for DBAs, MySQL

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

… execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice exercise for checking the…_INCREMENT=22187769 DEFAULT CHARSET=utf8; The table had 22 million rows, with approximately 8 million of them having role_id = 1…-party, independent and technical opinion about the new features of MySQL query planner? Are you not yet familiar with terms like MRR…

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: , ,

Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

August 28, 2007 By 57 Comments

…): mysql_connect(“127.0.0.1″, “root”); mysql_select_db(“test”); for ($i = 0; $i < 10000000; $i++) { $b = $i % 1000; mysql_query(“INSERT INTO count… perform some query on this table using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count…

Filed Under: Benchmarks, Insight for Developers Tagged With:

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

…rows from the ibd file and import them into the database. The time between the deletion of rows…/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513 [...] INDEX: name PRIMARY, id…date); Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) …

Filed Under: Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

…FTS_DOC_ID column, however, MySQL apparently has no problem with either of the following statements: mysql> insert into dir_test_innodb3 …+—————————+———+ 10 rows in set (0.00 sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql>…

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

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

… shows us which rows in Bar are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql> INSERT INTO Bar (ID,X… SQL queries for quality control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO test.MY_KEY_COLUMN_USAGE…

Filed Under: Insight for DBAs, MySQL

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

… example when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will… made columns i1 and i2 independent in this case each selecting about 1% rows from this table which contains about 10M rows. mysql [localhost... columns i1 and i2 highly correlated. In fact they are now the same: mysql [localhost] {msandbox} (test) > update idxtest set i2=i1; Query…

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