June 19, 2013

Post: How expensive is a WHERE clause in MySQL?

October 31, 2008 By 17 Comments

… ran it with a trivial WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do I need SQL… query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008-10… that the MySQL optimizer chooses to apply the clause to indexes. (However, as I found out some time ago, some date functions are…

Filed Under: Benchmarks, Insight for DBAs Tagged With:

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

February 26, 2013 By 1 Comment

….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_STOPWORDS.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15… had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table dir_test… index and associated processing for an InnoDB table.” The documentation also tells us that we can keep our FT indexes up to date…

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

Post: Full Text Search Webinar Questions Followup

August 24, 2012 By 5 Comments

… to create an InnoDB FT index before declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out of… indexes stay up to date with changes (inserts, updates and deletes)? That’s a good question, because both of these external indexing technologies… where I announced the webinar: We evaluated MySQL vs Solr.  I was able to index all the data from the database into…

Filed Under: Events and Announcements, Insight for Developers, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… note that the following changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer…_orderkey and o_orderdate >= ’1993-08-01′ and o_orderdate < date_add( ’1993-08-01′ ,interval ’3′ month) and l_returnflag… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…

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

Post: Impact of the sort buffer size in MySQL

October 25, 2010 By 8 Comments

… created a MyISAM table with one char column without an index: CREATE TABLE `sorttest` ( `data` char(30) DEFAULT NULL ) ENGINE=MyISAM…=100000 COUNT=0 while [ "$NUMROW" -gt "$COUNT" ] do UUID=`uuidgen` mysql test -e “insert into sorttest value (‘$UUID’);” let “COUNT=COUNT…/bash for i in `seq 1 1000` do START=`date +%s.%N` OUT=`mysql -e “set session sort_buffer_size=32*1024…

Filed Under: Benchmarks, MySQL Tagged With: ,

Post: How (not) to find unused indexes

October 16, 2009 By 2 Comments

… index is woeful, but provided that the application is always only sending query 1 to MySQL it’s actually a pretty good index… where customers have queries that use a BETWEEN on a date, but the window of time it is searching in is…  In this case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really…

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

Post: Using index for ORDER BY vs restricting number of rows.

February 16, 2007 By 8 Comments

… on such as seller, price, date added etc. Such configuration often causes serious challenge choosing proper index configuration as it is hard… to be fully indexed. An extra problem comes from the fact MySQL prefers when it is possible to use index for further restriction and than using file sort, rather than using index for…

Filed Under: Insight for Developers Tagged With:

Post: MySQL extensions for hosting

April 24, 2008 By 11 Comments

….00 sec) mysql> SHOW INDEX_STATISTICS LIKE ‘hosting2%’; +——————————————–+———–+ | Index | Rows_read | +——————————————–+———–+ |…PRIMARY | 67 | | hosting2.wp_posts.type_status_date | 459 | | hosting2.wp_term_taxonomy.term_id_…

Filed Under: Percona Software Tagged With:

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

…(‘html’,'video’,'mp3′,’image’,'pdf’,'other’) NOT NULL, `message_day` date NOT NULL, `mod_is` tinyint(3) unsigned NOT NULL default…=art.link* –datadir=/mnt/data/mysql/ which will show something like this: table: art/link_out104, index: PRIMARY, space id: 12, root… this is in XtraDB/InnoDB plugin – fast index creation. With this feature, InnoDB creates indexes by sort, so page fill factor should…

Filed Under: Percona Software Tagged With: , ,

Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)

June 19, 2012 By 2 Comments

Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them … small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date. Here is example of gains… in this table is using “fast_index_creation=0″ which allows to disable fast index creation in Percona Server and force complete…

Filed Under: Benchmarks, Insight for DBAs, MySQL
« Older Posts
Newer Posts »