If you use functions on the ‘left hand side’ of a where clause, you ignore any indexes. “and left(a, 10) = ’2008-10-29′” should be “and ’2008-10-29′ = left(a, 10)”
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 the column even if function is very…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 8 Comments
… MySQL Stored Function and we’ll be good to go. It worked! But not so well really – building the index, which was taking 10 minutes, was now taking 16 minutes. Then we added another MySQL function for different set of attributes and indexing speed went from 16 minutes to 26…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… one have optimized indexes and sorting in such a scenario, since inequality ends index usage. Actually in this case index on (A,B…: We have a database that has about 400GB of indexes. The indexes can’t fit in memory anymore. How does this affect… in index order MySQL gets data in already sorted order and looks at “one group at the time”, computing aggregate functions as…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… important, as we will see below) scan the indexes on indexed_colA and indexed_colB and then do a set-theoretic union of…), … other columns here … PRIMARY KEY(user_id), INDEX `parent_id` (parent_id), INDEX `status` (status), INDEX `user_type` (user_type) ) ENGINE=InnoDB; SELECT… has some new optimizer functionality that you end up missing out on. Junior developers might see USE INDEX() and suddenly start believing…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… 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 there are a bunch of subtleties, but I will…
Post: Bug#12704861
November 20, 2011 By 1 Comment
… index tree would be corrupted. The correct solution is to make the freed clustered index pages unavailable to the BLOB allocation. This function…-transaction. btr_freed_leaves_validate(): A debug function for checking that all clustered index leaf pages that have been marked free in…. buf_reset_check_index_page_at_flush(): Remove. The function fsp_init_file_page_low() already sets bpage->check_index_page_at_flush…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… agree – regardless of what database is used, abstracting out core functionality usually means more portable code. You’re right that when… the needs of the application. > 2. Not using auto_increment functionality This is right. With some > exception however. For example Innodb… remember > we’re here to help. Partial agreement: “Use Indexes smartly.” Indexing columns that have very low cardinality will often be a…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…_eventsblogpercona.commanual Com_show_fieldsblogpercona.commanual Com_show_function_codeblogpercona.commanual Com_show_function_statusblogpercona.commanual Com_show_grantsblogpercona.commanual Com_show… log_binblogpercona.commanual log_bin_indexblogpercona.commanual log_bin_trust_function_creatorsblogpercona.commanual log_errorblogpercona.commanual log_isamblogpercona.commanual log_outputblogpercona…
