… on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you … the primary key solution for random selection only work when the IDs… every solution for randomly choosing rows involves some compromise, either of …in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); …
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…– older partitions cover larger timespans. The last few partitions are one week each….MySQL. PARTITIONs are scanned one at a time. (Please provide specific references if you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are …
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
Last time I wrote about a few tips that can make …: mysql> pager md5sum PAGER set to ‘md5sum’ # Original query mysql> SELECT … 32a1894d773c9b85172969c659175d2d – 1 row in set (0.40 sec) # Rewritten query – wrong mysql> SELECT … fdb94521558684afedc8148ca724f578 – 1 row…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… the following query in particular that had this surprising outcome: mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like… to execute: mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’\G 266 rows in set (1… prepared for the MySQL 5.6 release, and get advantage of the latest integrated tools that MySQL provides with its last GA release…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… call edit: mysql> edit and it will open your default text editor with the text of the last query. The … mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row …
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
…168.0.180 root@192.168.0.180′s password: Last login: Fri Feb 22 23:27:49 2013 from …transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row *************************** Success!: Hello World! 1 row in…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…create(‘demo’, ‘dashboard_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid,’…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… indexes: mysql> select * from all_indexes limit 1 \G *************************** 1. row *************************** TABLE_SCHEMA: sakila TABLE_NAME: actor INDEX_NAME: idx_actor_last_name NON… select my droppable (unused) indexes from this view: mysql> select * from droppable_indexes; +————–+—————+—————————–+ | table_schema | table_name | index_name | +————–+—————+—————————–+ | sakila | actor | idx_actor_last…
