June 20, 2013

Post: MySQL EXPLAIN limits and errors.

July 24, 2006 By 6 Comments

… executed – will in add index live as it can now do on Cluster ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In MySQL 5.0… work with in memory temporary table. The other thing – since MySQL 4.1 sorting can be done with storing row data in…

Filed Under: Insight for Developers Tagged With: ,

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… 4*SATA disks in RAID10. I used 10mil row table which would look as following in terms of data and index size: mysql… become very slow when index does not fit to memory any more. Creating Index by sort is great Creating index by sort can… run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation it can make…

Filed Under: Insight for DBAs, MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…. Let’s start with a table containing 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT… table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER… table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast index creation. So we…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

… with >= MySQL-4.1.1 and stored already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY… having data corrupted in secondary indexes, in which case simple OPTIMIZE TABLE could be enough to rebuild it, but it is much…) == dict_index_get_page(index)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

….30 and MySQL 5.6.10 with no configuration tuning other than to set innodb_ft_min_token_size to 4 (rather… we’ve sorted this out, let’s drop and rebuild our FT index on the InnoDB table and try the above queries…_min_token_size to 1, dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT id, full_name, MATCH(full_name, details…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…rebuild the table. -rw-rw—-. 1 mysql mysql 98304 Feb 20 18:57 FTS_0000000000000025_ADDED.ibd -rw-rw—-. 1 mysql mysql…indexes, it’s entirely possible to DROP multiple FT indexes… +———————-+———-+———-+———-+ 1 row in set (4.60 sec) mysql> select * from information_…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Comment: Why MySQL could be slow with large tables ?

November 29, 2006 By 141 Comments

Normally MySQL is rather fast loading data in MyISAM table, but there is exception, which is when it can’t rebuild indexes by… having large (does not fit in memory) PRIMARY or UNIQUE indexes. Below is the internal letter I’ve sent out on….3G, 15.000.000 rows, 512MB memory one the box. MySQL 4.1.8 One could could call it trivial fast task…

Filed Under: Insight for DBAs Tagged With: ,

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

… rebuild to complete. If you’re unlucky i.e. you have a lot of indexes… swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table_new`…violate mysql data file structure, so be really careful with that one. Default values. MySQL rebuilds …

Filed Under: Insight for DBAs Tagged With: , ,

Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

December 9, 2010 By 18 Comments

… status | OK | +——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table a add key…indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Post: InnoDB: look after fragmentation

November 5, 2009 By 12 Comments

… | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name | Value | … is possible in XtraDB / InnoDB-plugin with FAST INDEX creation) so be careful with queries scan …

Filed Under: Insight for DBAs, MySQL Tagged With:
« Older Posts