…index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index… and bad query execution plans. a. Bad Plan…Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site… and order by to be fully indexed. An extra problem comes from the fact MySQL prefers when it is possible to use index for… is badly selective using index scan is much better idea. Until MySQL is able to handle this you will have to use force index hint…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on… arithmetic operation. Same can apply to order by, if you would like that to use the index for sorting. There are however some…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…index and FULLTEXT index, while other combinations may also make sense. Note: Order of columns in index is significant, index…index will also be able to use longer index. Unlike with duplicate indexes, there are however cases when redundant indexes are helpful – typically if longer index…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… is used so using values generated elseware > might be faster. I disagree with you on this completely because InnoDB uses a clustered index based on (in order… having the benefit available. Making a blanket statement that using relations is bad is unwise. > 5. The n+1 pattern This probably…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…that bad, but it can be downright abysmal in some respects. Declarative referential integrity index …resulting in horrendous performance by using one of these foreign key index with low cardinality … references items(operator_id) ); create index order_date_idx on orders(order_date); Okay so we know …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… updating them ? Also note many simple “queries” (using constants for index accesses) will not use index cardinality data at all but will estimate number… plans are good or bad independently of it being run there is need to bother – for bad plans use FORCE INDEX or change the… are very inexact. Fortunately for most queries which use these stats accuracy at the order of magnitude is enough. Sometimes it is…
Post: Pacemaker, please meet NDB Cluster or using Pacemaker/Heartbeat to start a NDB Cluster
May 19, 2010 By 9 Comments
… about the same time, it is not too bad but what if one the critical node takes …> Please note the ndbd resource is defined through the use of a clone set. The clone set will … root@testvirtbox:~# crm configure show INFO: building help index INFO: object order-1 cannot be represented in the CLI …
Comment: How much overhead is caused by on disk temporary tables
August 17, 2007 By 26 Comments
… decided I was for making users writing bad SQL to suffer instead of forcing to use ORDER BY NULL if you want results… be unique). So ironically, although MySQL could in principle use the index on the id column to do a pretty fast GROUP… commitment to returning the rows in the order implied by the GROUP BY clause. ORDER BY NULL is a nice trick…to…
Comment: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…€ attitude. Ordered data reads The down side of two phase index retrievals is one you identified in your performance study – bad performance… system, not all index entries correspond to records visible by the current transaction; to use the index directly the index must also contain enough…
