…: mysql> select ltrim_junk_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql> select remove_dups_mysql(author… function, I could have written it as an SQL statement: mysql> select REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( LOWER(author), ‘aa’, ‘a’), ‘bb’, ‘b’), ‘cc’, ‘c’), ‘dd’, ‘d…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… of size 800000 alloc’d ==9090== at 0x4C28FDF: malloc (vg_replace_malloc.c:236) ==9090== by 0x6BE309B: ut_malloc (ut0mem.c…:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option and… Com_replaceblogpercona.commanual Com_replace_selectblogpercona.commanual Com_resetblogpercona…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…dimension columns from the projection, instead replacing them with the dimension keys: select dim_date.date_id, origin.airport_… there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…does during its operation. It also takes care of atomically* replacing the new contents of the view with the old …than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
…_id` (`group_id`,`parent_id`,`published`) As you can see MySQL selects to use “ref” access only using two first key parts… rows accessed causing their pages to be loaded replacing valuable data from cache). MySQL could look at the stats and adjust them…>Const we can see MySQL selecting (A) index instead of (A,B) or even worse it can can select something like (A,D…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… part is used. Let us now replace the range with IN list in this query: mysql> explain select sum(events) from info where… DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from dl limit 5; +————+ | myday | +————+ | 2001-01-01 | | 2001-01…: mysql> select * from g; +—–+ | gr | +—–+ | 10 | | 20 | | 30 | | 40 | | 50 | | 60 | | 70 | | 80 | | 90 | | 100 | +—–+ 10 rows in set (0.00 sec) mysql…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…: mysql> select * from v2 where c1 = 100; Empty set (1.64 sec) You can see that MySQL is accessing millions of rows: mysql> explain select… params table. Place parameters into the params table using the REPLACE statement: mysql> replace into params (`view_name`, `param1_val`, `connection_id`) values…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… I actually went ahead and manually edited test.ibd file replacing few bytes so corruption is mild. First I should note… values mysql> select max(id) from test2; +———+ | max(id) | +———+ | 220 | +———+ 1 row in set (0.00 sec) mysql> insert ignore into test2 select * from…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number …cache efficiency. First looking at number of your selects – Com_select and see how many of them are …by (Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits . These are some numbers you can…
