Linked Questions

32
votes
2answers
75k views

What is the difference between optimize table and analyze table in mysql

What is the difference between optimize table and analyze table in mysql? I have read the online docs, but not sure what the difference is.
16
votes
3answers
6k views

Problem with MySQL subquery

Why does this query DELETE FROM test WHERE id = ( SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1 ); sometimes delete 1 row, ...
11
votes
2answers
6k views

Any gotchas at all with converting from MyISAM to InnoDB?

I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
2
votes
2answers
4k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
5
votes
2answers
3k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE `...
6
votes
1answer
5k views

Suddenly have to rebuild indexes to prevent site from going down

I have been having this problem for a while: https://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column It seems all is good and when I suddenly get a lot of writes to the ...
3
votes
1answer
5k views

How can I find out the last time a MySQL table was analyzed?

I'm interested in this for InnoDB mostly, but also for MyISAM. I looked around in information_schema and did not see this data anywhere. I'm using MySQL 5.5.16.
3
votes
1answer
2k views

Need help to understand how information_schema.TABLES get updated

I created large innodb table mysql> select count(*) from big_data; +----------+ | count(*) | +----------+ | 23822336 | +----------+ checked the stats in informaion_schema.TABLES mysql> ...
4
votes
2answers
2k views

Could running ANALYZE on all databases on large InnoDB tables show some performance increase?

So we have an old database that was fragmented until we ran OPTIMIZE on all tables. The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity. There are so many ...
0
votes
1answer
1k views

Is ANALYZE useful immediately after creating an InnoDB index?

Let's create a new index on an existing table: CREATE INDEX `IDX_some_index` ON `some_table` (`a_column`, `another_column`); Is this useful? ANALYZE TABLE `some_table`; Can anyone point to ...
3
votes
1answer
2k views

Strange things with Avg_row_length

The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366. Later I dumped table with mysqldump, droped table, then created again ...
2
votes
1answer
600 views

Why isn't the rows of an EXPLAIN accurate?

I have the following table: mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.03 sec) I do an EXPLAIN: mysql>...
1
vote
1answer
454 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
3
votes
2answers
544 views

Can I use a foreign key index as a shortcut to getting a row count in an INNODB table?

I have a table that has a large number of rows in it. The primary key (an auto-incrementing integer) is, by default, indexed. While waiting for a row count to be returned I did an EXPLAIN in another ...
2
votes
2answers
50 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...