…` tinyint(4) DEFAULT NULL, `FlightDate` date NOT NULL, `date_id` smallint(6) NOT NULL, PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year…=latin1 COMMENT=’Contains the date information from the BTS ontime flight data. Note dates may not be in date_id order’; /*!40101 SET…(10) DEFAULT NULL, `Div5TailNum` varchar(10) DEFAULT NULL, KEY `date_id` (`date_id`), KEY `flight_id` (`flight_id`), KEY `origin_airport_id…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
…> explain select * from tst where i=5 and date(d)=date(now()) order by date(d) \G *************************** 1. row *************************** id: 1 select_type… select * from tst where i=5 and date(d)=date(now()) order by unix_timestamp(date(d)) \G *************************** 1. row *************************** id: 1 select…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… now so index on (category_id, date_created) can’t be used directly. Index on date_created separately would still work. The… columns, with worse then perfect selectivity. Various social networking and dating sites are perfect example of such queries SELECT FROM people… on particular constant and other factors. For example for our dating example if we use only (last_online) index and look…
Comment: Full text search for all MySQL Storage Engines
June 29, 2006 By 37 Comments
… been using SPH_MATCH_ALL along with SPH_SORT_DATE_DESC/SPH_SORT_DATE_ASC. Regarding relevance the problem is that the… posts which still matches unless you use the sort by date instead of sort by relevance (where sort by relevance is… finally pick up the 200 best matches (desc by date or asc by date). As you can see the profile for sphinx…
Post: Handling big result sets
June 26, 2006 By 13 Comments
…` ( `f1` int(11) NOT NULL auto_increment, `f2` date default NULL, `f3` date default NULL, `f4` varchar(14) default NULL, `f5` varchar…) default NULL, `f18` int(11) NOT NULL, `f19` date default NULL, `f20` date default NULL, `f21` varchar(14) default NULL, `f22` varchar(6) default NULL, `f23` date default NULL, `f24` smallint(6) default NULL, `f25` smallint(6…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… ENCLOSED BY ‘”‘ LINES STARTING BY ‘salaries\t’ (emp_no, from_date, salary, to_date); Query OK, 3 rows affected (0.01 sec) Records… * from salaries where emp_no=10008; +——–+——–+————+————+ | emp_no | salary | from_date | to_date | +——–+——–+————+————+ | 10008 | 46671 | 1998-03-11 | 1999-03-11 | | 10008…
Comment: Joining on range? Wrong!
May 19, 2010 By 10 Comments
… / datetime column with additional date column. then if you need date without time, better use it on date column: create table x( id int, something chat(40), created datetime, created_date date, primary key(id), key(created), key(created_date) ); then: select * from x where created_date = ’2010-01-06…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
… | A | 1047467 | NULL | NULL | | BTREE | | | posts | 1 | idx_post_date | 1 | date | A | 1047467 | NULL | NULL | | BTREE | | | posts | 1 | idx_posts_userid… | | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ mysql> explain SELECT posts.* FROM posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts.userid WHERE follow…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
… takes place: Node1 > PDT > select * from foo.bar; +—-+————-+———————+ | id | data | date_created | +—-+————-+———————+ | 1 | node1, row1 | 2012-09-24 09:38:49…:38:49 | +—-+————-+———————+ Node2 > EDT > select * from foo.bar; +—-+————-+———————+ | id | data | date_created | +—-+————-+———————+ | 1 | node1, row1 | 2012-09-24 12:38:49…
Comment: MyISAM concurrent insert
August 23, 2011 By 12 Comments
… ID,Date, and indexed Date, with the Select statements always: Select from table where ID = ###### Order by Date with sometimes AND Date >= 20100101 and Date <= 20110101 (for ranges of between specific dates)
