June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…key solution for random selection only work …a different order from what you specify…multiple columns in a compound index, then the order would matter, and it would have to match the order of columns…MySQL to scan the `title` table first, grouping by kind_id in index order.  This made the first table…

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

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…table built on it is greater than the join_buffer_size, then multiple hash tables…selective WHERE clause on an indexed column The SQL used for this test together with its EXPLAIN output as returned by MySQL…joining key from the table supplier would match approximately 600 rows from the table lineitem …

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

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

March 4, 2013 By 7 Comments

…mysql: SELECT id, title, MATCH(title, body) AGAINST (‘arizona business records’ IN NATURAL LANGUAGE MODE) AS score FROM…multiple matches containing the words “James” and “Peterson”, and we should expect the record from…

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

… table changelogs which are created by FlexCDC. The refresh algorithm computes changes in multiple …tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table…this column. It is used to prevent wide innodb primary keys on the MV. mysql> select…

Filed Under: Insight for DBAs, MySQL

Post: Multiple column index vs multiple indexes

August 22, 2008 By 13 Comments

…multiple columns: Q1 SELECT sum(length(val)) FROM T WHERE i=2 AND j=1 Q2 SELECT sum(length(val)) FROM…Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL…

Filed Under: Benchmarks, Insight for DBAs

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

August 30, 2012 By 2 Comments

… column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from …real application which would use multiple table and the column type was inconsistent between them… so inserts into one table would happen with …

Filed Under: Insight for DBAs, MySQL

Post: Talking MySQL to Sphinx

April 19, 2009 By 4 Comments

…tests I used the table from the forum search… couple of extra columns to result set…select max(author_id) as a ,forum_id as f from sptest where num_links=1; Empty set (2.70 sec) MySQL mysql> select…from Sphinx however comes from its ability to scale almost linearly using multiple CPU cores and multiple…

Filed Under: Insight for Developers Tagged With:

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

…in mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_…, you can concatenate MyISAM files, even when multiple keys are defined. Not for everyday… 6 rows in set (0.00 sec) So varchar columns are supported without any issue but,…

Filed Under: MySQL

Post: 3 ways MySQL uses indexes

September 12, 2009 By 28 Comments

…column indexes which are used but only to their short prefix which is not very selective. A lot of this mistakes come from…MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple…(note MySQL may not select to use index for sort if you sort full table …

Filed Under: Insight for Developers Tagged With:

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

February 26, 2013 By 1 Comment

…TABLE, and that we might have to run OPTIMIZE TABLE multiple times if we’ve had a lot of changes to the table…mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from…

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