June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If you … compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)?… the primary key solution for random selection only work when the IDs…there’s a gap of id’s between 47000 and 50000 with no …

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

Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6

August 30, 2012 By 2 Comments

… Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1…the real application which would use multiple table and the column type was inconsistent between them… so inserts into …

Filed Under: Insight for DBAs, MySQL

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

May 6, 2011 By 23 Comments

…so there are only 29 air carriers in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** … common query features like BETWEEN or IN. It is also possible to spread your data over multiple machines, scaling out…

Filed Under: MySQL

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…_blocks_time=1000 on MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5.6.10 has…_discount between 1 and 3 and lo_quantity < 25\G *************************** 1. row *************************** revenue: 446268068091 1 row in set (12.36 sec) mysql> select * from…

Filed Under: MySQL Tagged With: , , , , ,

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

May 16, 2011 By 5 Comments

…“words” by duplicating the entire set multiple times. mysql> load data infile ‘/tmp/words’…run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)),…select min(result) == 0 is_prime from ( select mod({$NUMBER_TO_FACTOR}/prime_number) from dataset where id between…

Filed Under: MySQL

Post: Using UNION to implement loose index scan in MySQL

August 10, 2006 By 15 Comments

… multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT name… row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN… well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION ALL -> SELECT name FROM people…

Filed Under: Insight for Developers Tagged With: ,

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

February 26, 2013 By 1 Comment

…. The second part will compare query results between MyISAM FTS and InnoDB FTS over the … indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same …mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select…

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

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

… algorithm computes changes in multiple transactions. After each …mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…orders: mysql> delete -> from order_lines -> where order_id -> between 1 …

Filed Under: Insight for DBAs, MySQL

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

… in set (4 min 58.63 sec) mysql> select count(pad) from large where val between 1 and 100; +————+ | count(pad) | +————+ | 314008 | +————+ 1… have whole bunch of problems solved just doing so. Use multiple servers to host portions of data set. Store portion of…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title…arizona” then we might expect to get back multiple matches containing the words “James” and “Peterson”,… Calculation of match score is completely different between the two engines; sometimes this leads to…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts