… 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: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…you need first few rows, even if some extra filtering takes place so you need to scan more … if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table | type | …
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: 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: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…set, all in parallel. All joins, aggregation and filtering is done at the storage node level. This…resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …threshold of 128 items * The following projections were selected for a UNIQUE CHECK * on the base table: …
Post: Why audit logging with triggers in MySQL is bad for replication
September 29, 2008 By 10 Comments
… -lt binary – Check out http://hackmysql.com/mysqlsla_filters for how to filter by statement. Looking through the binary logs I could…. The trail is kept using two triggers on that table. select trigger_name, event_object_table, Event_Manipulation from information_schema…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… help you with some strategic decisions. Sample outputs: mysql> SHOW USER_STATISTICS LIKE ‘hosting2′\G *************************** 1. row *************************** … only information_schema support to allow SELECT query transformations, joins and better filtering for the statistics Also combining …
Post: MySQL Blob Compression performance benefits
January 11, 2008 By 17 Comments
… makes sense to keep it compressed. Unfortunately MySQL does not provide compressed BLOB/TEXT columns (… you ability to do some server side filtering (which however would not be overly fast …much compression will benefit you. SELECT AVG(LENGTH(body)) FROM data vs SELECT AVG(LENGTH(COMPRESS(body))) …
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…or DELETE and forgets the where clause or filters weren’t quite right The application had …”FLUSH TABLE WITH READ LOCK”. Namely when a select is blocking the flush from completing and …, long term backups Decompressing Percona XtraBackup for MySQL in parallel using all your resources available …
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…a look at the configuration used with different MySQL flavors. MySQL 5.5.24 Configuration innodb_file_…| select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | …
