…column, and do a range primary key lookup as follows: SELECT non_key_column from tbl WHERE pk_column IN…query times remain under a minute. So when the correct query execution plan is not used, there is no difference in query…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…column to trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query…index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD KEY (c), ADD KEY(c); Query… optimization is not applicable: UNIQUE indexes in ALTER TABLE are ignored to …
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
… With this in mind, I modified Vadim’s queries to use the FlightDate column in the WHERE …word-spacing:0;background-color:#fff;z-index:1;border-top:0px none;border-…queries use subqueries in the FROM clause, which Shard-Query does not yet add parallelism to. I plan to add support for those queries…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…in set (0.00 sec) Such automatically table generated names are not really helpful for multiple column indexes…add indexes indexes can get different names which makes scripted upgrade and downgrade processes complicated. If you use auto generated index…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… long strings columns In the benchmark for this query we have 10 queries per sec. Usuall technique we use for such queries is covering index, which store all needed columns in the index, and… – we can extend index `state_id_idx` (`state_id`) by two columns: ALTER TABLE userinfo DROP KEY state_id, ADD KEY `state_id…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…index from column (a) to column (a,b) right which will make it faster and should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
…index configuration as it is hard to add all combinations of restrictions and order by to be fully indexed…in this case MySQL needs to sort a lot of rows to display only few. If we force index as in second query…would need to better take into account column selectivity together with LIMIT range. If …
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…index length. It means both index columns will be used to execute the query…column and using it for filtering instead: ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD INDEX…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…in set (0.00 sec) mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And… for the sake of simplicity. Let’s start with this query, which returns zero rows but does a full table scan…
