…filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough gotchas with replication itself and getting things right with filtering…ve seen such filtering being broken by developer forgetting this restriction and running one time update query without selecting proper default …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M… plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL… where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range…
Comment: MySQL VIEW as performance troublemaker
December 21, 2009 By 57 Comments
…, first select (view) is SLOWLY, then simple select (it’s obvious, becouse “group by” with index filtering is slowly, that simple index filtering). Problem…: mysql> CREATE VIEW user_counts AS SELECT user_id, comment_id, message FROM comments; Query OK, 0 rows affected (0.02 sec) mysql> SELECT…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… `status` (`status`) ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1; mysql> SELECT count(*), status FROM sales GROUP by status; +———-+———+ | count(*) | status | +———-+———+ |Â…=latin1; mysql> SELECT count(*) FROM Country; +———-+ | count(*) | +———-+ |     239 | +———-+ 1 row in set (0.00 sec) mysql> SELECT count(distinct… lead yourself into a trap where your index can not filter out enough rows to be effective. I see this…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
…of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you… interval (ie after I’ve done some changes) or filter out queries which you already looked at using LIKE… get new queries in the end you can do SELECT * FROM (SELECT * FROM slow_log ORDER BY start_time DESC…
Post: Using Sphinx as MySQL data retrieval accelerator
October 30, 2006 By 25 Comments
… efficient indexes so selectivity is done by index and index used to resolve order by. Funny enough to filter by attributes or… be used with other databases which may be slower than MySQL. MySQL Make it so one would not need to use sphinx… parallel processing, fast count(*) and bitmap indexes to help non selective clauses. Also some form of fast sort like priority queue…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…atomically, not just for new connections as in MySQL. This is very helpful for measurement as …_blog_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload …query, by using a LIMIT clause or by filtering based on the option_name column, or …
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
… query: mysql> select * from information_schema.user_statistics where user=”#mysql_system#” \G *************************** 1. row *************************** USER: #mysql_system# TOTAL… replication thread: mk-query-digest slow-log –filter ‘($event->{user} || “”) =~ m/[SLAVE_THREAD]/’ > /tmp/report-…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the… the active master’s buffer pool with the following query: select table_schema, table_name, page_type, count(*) from information_schema… active master: mk-query-digest –processlist h=active \ –filter ‘$event->{arg} =~ m/^SELECT/i’ –execute h=passive After a bit I CTRL…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 21 Comments
…be some false positives but these are filtered out after reading the data so …mysql> explain select url from 124pages.124pages where url_crc=484036220 and url=”http://www.dell.com/”; +—-+————-+———-+——+—————+———+———+——-+——+————-+ | id | select…
