You must refactor the query to perform WHERE, JOIN and GROUP BY clauses IN THAT EXACT ORDER !!!
Here is the refactored query :
select
sq.search_id,
count(1) search_id_count
from
(select search_id from search where search_id > 47000) sq
inner join
(select search_id from search_hit where search_id > 47000) ssh
using (search_id)
group by
sq.search_id;
Running OPTIMIZE TABLE (reduces table fragmentation and recreate indexes) may not be necessary unless you do heavy INSERTs, UPDATEs, DELETEs. I'll say it is optional.
Give it a Try !!!
You have 6 indexes
PRIMARY KEY (`UID`),
KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),
The PRIMARY KEY is in the gen_clust_index (aka Clustered Index). All secondary index entries include a corresponding PRIMARY KEY entry.
I would mysqldump that table and reload it into a test DB server
Next, I would run CHECKSUM TABLE db1.tableABC; or mysqlchk against db1.tableABC in production and the test DB.
If the checksum values match, you should be OK.
If they do not match or you are not sure, run this on the production server
ALTER TABLE db1.tableABC ENGINE=InnoDB;
This will rebuild the table and its indexes.
If that error ever materializes after this, there may be a data dictionary problem inside ibdata1. Your final solution would be to dump all databases, shutdown mysql, delete ibdata1, ib_logfile0, ib_logfile1, start mysql, reload all data.
I posted this InnoDB Cleanup Process in StackOverflow back on Oct 29, 2010
Best Answer
When you load a table, the order of the rows can affect the shape of an index.
If you ran
ALTER TABLE generic_dummy_table_name ORDER BY datetime;, then any index whose first column isdatetime, will be load in a lopsided fashion. Under that circumstance, doingALTER TABLE ... ORDER BY some-columnwill always create a badly fragmented index.Please see my
Oct 26, 2012post How badly does innodb fragment in the face of somewhat out-of-order insertions? where I explain how a BTree get fragmented when you order the data by a specific column in a table. Even though the post is about InnoDB, the BTree mechanics I discussed in that post apply just as much to MyISAM.I also discussed these mechanics in Benefits of BTREE in MySQL (
July 28, 2012)Your only fix is to not mess with the physical order of rows when restoring a table.
YOUR INDEX SIZE
From the number in the question, your
.MYIfile is 50GB ??? You need to identify what indexes are redundant and remove them. Please goto MySql - find redundant indexes. There are two nice answers there how to find them and generate SQL to remove them.