June 17, 2013

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

…SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL…SELECT will only be faster when the condition is very selective…

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

Post: Fun with the MySQL pager command

January 21, 2013 By 6 Comments

…only compare the checksum: mysql> pager md5sum PAGER set to ‘md5sum’ # Original query mysql> SELECT … 32a1894d773c9b85172969c659175d2d – 1 row in set (0.40 sec) # Rewritten query – wrong mysql> SELECT…

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…but my query is only selecting on vendor, will the index have any impact on the speed of the query? A: If… After running an analyze on a table with 11M rows I’ve seen cardinality range from 19 to …scanning data in index order MySQL gets data in already sorted order and looks at “one group at the time…

Filed Under: Insight for DBAs, MySQL

Post: MySQL Query Cache

July 27, 2006 By 70 Comments

…MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query…query, have extra space or use different case – these would be different queries for query cache. Only SELECT queries…

Filed Under: Insight for DBAs Tagged With:

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

… single directory with some subdirectories inside, one for every single index in the table: …recovery you can find lot of deleted rows, not only rows deleted accidentally. You should manually …Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select …

Filed Under: Insight for DBAs, MySQL

Post: Innodb performance gotcha w Larger queries.

August 28, 2009 By 7 Comments

…mysql_query_str) { /* Scan the MySQL query string; check if SELECT is the first word there */ ibool success; dict_accept(*trx->mysql_query_str, “SELECT…= FALSE; } } The “REPLACE 1000 rows” SQL doesn’t have “SELECT” so, InnoDB scan… is one more interesting thing with this code – The scan is only run…

Filed Under: Insight for Developers Tagged With:

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

April 4, 2011 By 20 Comments

…’,'COMPLETE’); Query OK, 1 row affected (0.00 sec) mysql> call flexviews.set_definition( ->flexviews.get_id(‘demo’,'dashboard_top_customers’), -> ‘select customer_id…BOTH’,NULL); Query OK, 0 rows affected (7.01 sec) The second one is 365x faster because it examines only the rows that changed. This…

Filed Under: Insight for DBAs, MySQL

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…to MySQL 5.5.3 Prior to MySQL 5.5.3 a statement that opened a table only … rollback; Query OK, 0 rows affected (0.00 sec) session1 > start transaction; Query OK, 0 rows affected (0.00 sec) session1 > select *… transactions tends to run for hours. Now everyday one of the tables was renamed and swapped …

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

…Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows… | Oscar Blakemore | 0 | | 1442048 | Donald Simmons | 0 | +———+——————+——————-+ There’s only one row in the table that actually matches all …

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

… create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0 rows affected… MySQL 5.6 manual, and the only page which appears to suggest how using an explicitly-defined column is done is this one… WHERE id < 500000; Query OK, 245051 rows affected (8 min 13.13 sec) Records: 245051 Duplicates: 0 Warnings: 0 mysql> select * from information_schema…

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