… posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts.userid WHERE follow.userid=’61585′ ORDER BY date DESC LIMIT… | Null | Index_type | Comment | +——-+————+——————+————–+————-+———–+————-+———-+——–+——+————+———+ | posts | 0 | PRIMARY | 1 | id | A | 1047467 | NULL | NULL | | BTREE | | | posts | 1 | idx_post_date | 1 | date | A… posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts.userid WHERE follow.userid=’61585′ ORDER BY date DESC LIMIT…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…(operator_id) references items(operator_id) ); create index order_date_idx on orders(order_date); Okay so we know that we’ll never… create the following indexes: unique index on order_id index on item_type_id index on locale_id index on currency_id index on operator_id…. Additionally I need an index on order_date to help with finding orders for a given date. 8 columns 6 indexes on single columns…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will… order by, if you would like that to use the index for sorting. There are however some interesting exception. Compare those…> explain select * from tst where i=5 and date(d)=date(now()) order by date(d) \G *************************** 1. row *************************** id: 1 select_type…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… row *************************** Table: dl Create Table: CREATE TABLE `dl` ( `myday` date NOT NULL, PRIMARY KEY (`myday`) ) ENGINE=MyISAM …: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY …
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…`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 …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… the du utility: InnoDB file size on disk: 42GB (with indexes) # du -sh * 203M ibdata1 128M ib_logfile0 128M ib_logfile1…` tinyint(4) DEFAULT NULL, `FlightDate` date NOT NULL, `date_id` smallint(6) NOT NULL, PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year…_row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL Create_time…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…) NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct… See there are new Extra notes USING INDEX condition; USING MRR. It would…
Comment: 3 ways MySQL uses indexes
July 20, 2010 By 28 Comments
that you see more of it, I created 2 new indexes (on production data, not testing or random). (sizes are different…_de` (`tree_type`,`date_from`,`date_to`,`tree_vector`,`tree_depth`), – the real key used in app FROM tree FORCE INDEX (ty_fr… ty_fr key_len 7 rows 11410 FROM tree FORCE INDEX (ty_fr_to) : key ty_fr_to key_len 10…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 8, 2007 By 22 Comments
….Subject, E.Private, DATE_FORMAT( E.Date, ‘%m-%d-%y’ ) As EDate, EHC.Hits FROM Entries E FORCE INDEX (DPrivate) LEFT JOIN EntryHitCounters….Subject, E.Private, DATE_FORMAT(E.Date,’%m-%d-%y’) as EDate, EHC.Hits from (select EntryID from Entries FORCE INDEX (DPrivate) where DiaryID=11693 and Private=0 Order By Date DESC LIMIT 1235,20) D…
Comment: ORDER BY ... LIMIT Performance Optimization
August 5, 2010 By 100 Comments
… category_id, date_created is indexed. EXPLAIN select newsid from news where category_id=53 and approval=’yes’ order by date_created desc… key key_len ref rows Extra 1 SIMPLE news index PRIMARY,category_id date_created 9 NULL 216 Using where the above…_keys key key_len ref rows Extra 1 SIMPLE news index NULL date_created 9 NULL 10 Using where Why does the…
