… by 2 times. Consider more extremal case: selectivity ~95%: SELECT cnt2 / cnt1 FROM (SELECT count(*) cnt1 FROM t2) d1, (SELECT count(*) cnt2 FROM t2 WHERE… claims MySQL will use index. Execution time: SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 – 1200 ms SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
…of filtered or partial MySQL Replication (as of version MySQL 5.0) – there …cases I’ve seen such filtering being broken by developer forgetting this restriction and running one time update query without selecting…, replicate-ignore-table, replicate-wild-do-table and replicate-wild-ignore-table options…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… send first 20 rows ignoring the rest. In this case MySQL has 2 indexes where … | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where (…
Post: How to obtain the "LES" (Last Executed Statement) from an Optimized Core Dump?
September 9, 2012 By 9 Comments
…5.25a-rel27.1/sql/sql_select.cc:312 #21 mysql_execute_command (thd=thd@entry=0x33acd30…” – or Last Executed Statement - in our RQG case for example, we could bring up …list, 3135 lex->duplicates, 3136 lex->ignore))) 3137 { 3138 res= handle_select(thd, lex, sel_result, OPTION…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…mysql/mysql.sock consult 28852 26909 0 19:08 pts/2 00:00:00 grep mysqld In this case,… query, I run EXPLAIN if possible (rewriting non-SELECT queries if needed, and using careful judgment …ignore. The above process may look like a static recipe that you can just mechanically execute, and in some cases…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…mysql-data-201202230157-binlog-info File Position Binlog_Do_DB Binlog_Ignore_DB mysql…mysql-bin.000022 | mysql [root@sb logs]# mysqlbinlog –verbose –base64-output=decode-rows –start-position 336797275 mysql-bin.000022 | mysql mysql> select… rollback procedure in case something fails during the …
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…, when he ran a slightly different query: SELECT MIN(t.update_time) FROM (SELECT uid, MIN(update_time) AS “…MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE…with Percona Server 5.5.25a, but in my case, the behavior was even stranger. I created a …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…Then your script can figure out which files it can ignore. What we’re doing here starts to look… If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it… gains are simply limited by some of MySQL‘s weaknesses in some cases. Not only that, but star schemas are …
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… is queries that execute slower in new MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * l_discount) as… mention here is that MySQL is wrong choosing index scan here. If we try to execute query with IGNORE KEY (li_shp_dt_idx) (full table scan will be performed in this case) then we got…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… tables reporting wrong checksums while running SELECT INTO OUTFILE and diffing these files… report different checksum in some cases. Rerunning check using BIT_XOR …issue for application and could be ignored. So at this point we …the best choice as with New MySQL version upgrade involves new Operating …
