… is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad…> explain select * from tst where i=5 and date(d)=date(now()) order by date(d) \G *************************** 1. row *************************** id: 1 select_type…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. …! We hypothesized that the MIN() function was doing some sort of…) [test]> select update_date FROM foo order by update_date LIMIT 5; +———————+ | update_date | +———————+ | 0024…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…: 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 needed. Q: Is there any special concerns or tricks for selecting using some date ranges? or between dates? or after a date…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… enough to fit wholly in memory: CREATE TABLE `t` ( `a` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t(a) values(current_date); insert into t select * from t; I repeated the last… chooses to apply the clause to indexes. (However, as I found out some time ago, some date functions are faster than others. That…
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: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… do SELECT * FROM sites ORDER BY date_created DESC LIMIT 10; I would use index on (date_created) to get result set very… in the list now so index on (category_id, date_created) can’t be used directly. Index on date_created separately would still work… expressions I guess this one is obvious – expressions or functions will block index usage for order by. Sort by column in leading…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…show_fieldsblogpercona.commanual Com_show_function_codeblogpercona.commanual Com_show_function_statusblogpercona.commanual Com_show_…commanual Created_tmp_tablesblogpercona.commanual datadirblogpercona.commanual date_formatblogpercona.commanual datetime_formatblogpercona.commanual debugblogpercona….
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…, back up, and restore. And the partitioning functionality is not done yet and not fully integrated… can we improve here? Especially assuming that there are indexes other than the primary key, we can shrink… create table ads_by_day_by_blueness ( day date not null, ad int unsigned not null, clicks…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… function: mysql> explain select sum(events) from info,dl where myday between ’2007-01-01′ and ’2007-01-31′ and d=date…: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… accessed by using a hash function on the values of the…`l_shipDATE` date DEFAULT NULL, `l_commitDATE` date DEFAULT NULL, `l_receiptDATE` date DEFAULT NULL… | 1 | SIMPLE | supplier | index | PRIMARY | i_s_nationkey | 5 | NULL | 20174 | 100.00 | Using index | | 1 | SIMPLE …
