June 19, 2013

Post: MySQL VIEW as performance troublemaker

August 12, 2007 By 57 Comments

… perform very inefficiently in many counts. MySQL has two ways of handling the VIEWS – query merge, in which case VIEW is simply expanded… of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set (0.00…_counts as select user_id,count(*) cnt from comments group by user_id; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_counts…

Filed Under: Insight for DBAs Tagged With: , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…of a column-level case-sensitivity requirement that …mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select…1028261 | | last_optimized_word | | | deleted_doc_count | 0 | | total_word_count | | | optimize_start_time | | | optimize_…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

… then see how the joins perform for each test case. Test Case A – Join a small table that fits in memory to… its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem… with its EXPLAIN output as returned by MySQL 5.5 is as follows: SELECT o.*, count(*) as num_items FROM orders AS o…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,

Post: Indexes in MySQL

June 2, 2006 By 29 Comments

… full table scan if selectivity over 25%. What with MySQL: mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1; +—-+————-+——-+——+—————+——+———+——-+——-+————-+ | id | select_type | table | type | possible… does MySQL choose index access? MySQL doesn’t calculate index selectivity, just estimates count of logical input/output operations, and for our case count of Logical…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

September 17, 2007 By 14 Comments

… | 290 | | 6950 | 290 | +——+—–+ 10 rows in set (21.12 sec) mysql> select count(*) cnt from dt where slack like “a%”; +———+ | cnt | +———+ | 2352996 | +———+ 1… has a little difference in this case as number of groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like… from single query ? Sure. Here is Way Number Three: mysql> select * from (select grp, count(*) cnt from dt where slack like “a%” group by…

Filed Under: Benchmarks, Insight for Developers Tagged With:

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1…that Shard-Query is faster in nearly every case. The performance of the queries is improved …

Filed Under: MySQL

Post: MySQL Query Cache WhiteSpace and comments

March 20, 2008 By 9 Comments

…mysql> select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (8.79 sec) mysql> select count(*) from fact where val like “%c%”; +———-+ | count…the case So in the nutshell you should be more free now in regards of some SELECT …

Filed Under: Insight for DBAs Tagged With:

Post: Using any general purpose computer as a special purpose SIMD computer

May 16, 2011 By 5 Comments

… as run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3… the MySQL client. This will be a linear operation because Shard-Query has no idea how to add parallelism in this case…. Likely current network technologies can not support such a degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id…

Filed Under: MySQL

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

… amazing MySQL manual, especially the option and ….commanual lower_case_file_systemblogpercona.commanual lower_case_table_namesblogpercona….Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…

Filed Under: Insight for DBAs, MySQL

Post: UDF -vs- MySQL Stored Function

September 6, 2010 By 8 Comments

… records: mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | 101533 | +———-+ 1 row in set (6.82 sec) mysql> select count(*) …However in case like this one where we have to process many records constantly and every second counts, UDF …

Filed Under: MySQL Tagged With: , ,
« Older Posts
Newer Posts »