… row *************************** Level: Note Code: 1831 Message: Duplicate index ‘col2_2′ defined on the table ‘test.test’. This is deprecated…Column types: # `col2` int(11) default null # `col3` varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column tables, incrementing column names in tables, and tables with the string NULL…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…was dropped and innodb_file_per_table is ON Frm file corrupt, zeroed out, lost or …INDEX_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX… 3. Recover SYS_TABLES, SYS_INDEXES, SYS_COLUMNS and SYS_FIELDS from indexes 0-1, 0-3, …
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1…| SIMPLE | testr | ALL | has_something | NULL | NULL | NULL | 15000000 | Using where | +—-+————-+——-+——+—————+——+———+——+———-+————-+ 1 row in…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… indexes on AGE and STATE columns. The better strategy is often to have combined multi-column index on (AGE… | idxtest | ALL | i1,combined | NULL | NULL | NULL | 11010048 | Using where | +—-+————-+———+——+—————+——+———+——+———-+————-+ 1 row…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…, `itm_prd_id` int(10) unsigned NOT NULL, `itm_order_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`itm_id`), KEY… is index on `tag_name` in `tags`, there is index on (`itm_prd_id`, `itm_order_timestamp`) in `items_ordered` and indexes on other columns used in…, but almost 120000 were scanned. And we have proper indexes on all necessary columns! What does EXPLAIN have to say about this? *************************** 1…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… like CREATE TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2), KEY K(id2)) engine… does a index be used having an index on one column and using order by on another column. do i need to add the index on column using order by clause. A: If index is…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… lot faster, especially for large indexes which would get inserts in very random order, such as indexes on UUID column or something similar. It… TABLE `a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c` char(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE… speed difference and more compact index in the end. So if you’re considering running OPTIMIZE on your tables consider using this…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… unsigned not null, order_amount decimal(10, 4) not null, order_date datetime not null, last_updated timestamp not null default current_timestamp on update… I need an index on order_date to help with finding orders for a given date. 8 columns 6 indexes on single columns. This is silly…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index… TABLE `t1000idx2` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `val` char(10) NOT NULL, KEY `i` (`i`,`j`) ) ENGINE=MyISAM…/null In the result table I compute per query results and present results in milliseconds. Query 1000 – 2 indexes 1000 – 2 columns 3…
