June 19, 2013

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

… application can create it does not …mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index…create isolated repeatable test cases based on your application, which can illustrate memory leak and when…

Filed Under: Insight for DBAs, MySQL

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

… see among MySQL users is how indexes are created. Quite commonly people just index individual columns as…the given index entry. So what choices does MySQL have if you have just 2 separate indexes ? I…of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should …

Filed Under: Insight for DBAs, MySQL Tagged With:

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

February 26, 2013 By 1 Comment

… never had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table dir_test… does InnoDB need to add a hidden column (similar to GEN_CLUST_INDEX when you don’t define a PRIMARY KEY, I assume) when… any other mixture of lettercase, you’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT…

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

Post: Recovery after DROP & CREATE

August 22, 2012 By 1 Comment

…-02-15 04:34:33″ How does the following “CREATE TABLE” make the recovery any harder? When a table gets dropped InnoDB deletes… in the pages. So, table_id and index_id of the dropped table gets overwritten. mysql> CREATE TABLE actor ( ->   actor_id SMALLINT UNSIGNED… # ./s_indexes /var/lib/mysql/ibdata1 15 0-18 0-19 # Apparently 0-18 is PRIMARY index and 0-19 is index idx_actor…

Filed Under: MySQL Tagged With: , ,

Post: Should you name indexes while doing ALTER TABLE ?

May 28, 2008 By 10 Comments

…index by the first column of index created, if there is such index already it will add numeric suffix to it, for example: mysql> create…indexes as part of upgrade process just because somebody was adding custom indexes to the box – this is especially concern for cases when…

Filed Under: Insight for DBAs Tagged With:

Post: When is MIN(DATE) != MIN(DATE) ?

September 4, 2012 By 5 Comments

…MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX…, running SELECT MIN(UNIX_TIMESTAMP(update_time)) …. does give a result of zero, whereas doing … was even stranger. I created a table with a similar structure: CREATE TABLE `foo` ( `i` int(…

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

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

…index can also use prefix of the new index are they ? It turns there are special cases when this is not the case. CREATE…work but it well often does not: CREATE TABLE `idxitest` ( `id` int(…INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index…

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

Post: How much space does empty Innodb table take ?

December 16, 2008 By 7 Comments

… if you have many indexes. How much space will Innodb take: mysql> create table test_innodb(a int…is very interesting – SHOW TABLE STATUS does not seems to show everything: CREATE TABLE `test_innodb` ( `i` int…sec) When at certain point you will see Innodb Free space to become non zero: mysql> show …

Filed Under: Insight for DBAs Tagged With:

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

…database background. > Things are different with MySQL. Good design will help dictate how…recommendation to normalize your data however it > does not always bring good performance. Joins are… factors that developers should consider when deciding when / how to create indexes. Foreign key references may…

Filed Under: Insight for Developers Tagged With: , ,

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in… taken from some MySQL server. 0. Create empty InnoDB tablespace. 1. Create the table: mysql>CREATE TABLE actor ( actor… cache! To refrain from such error ibdconnect does check if the space_id is already …

Filed Under: MySQL
« Older Posts
Newer Posts »