New answers tagged

2

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables ...


0

You clearly stated you goal in the question The goal is to significantly improve the performance of searches on the MyISAM table OK let's tune the MyISAM side of things. I have four(4) suggestions for you SUGGESTION #1 : Reduce your RAM First of all, look back 5 years ago and note what I said in my answer to the post What are the main differences ...


1

myisam_recover_options is not a dynamic variable. It is among the startup options for MyISAM. If you have set this option in my.cnf, mysqld must be restarted Please restart mysqld with # service mysql restart or if you have Windows C:\> net stop mysql C:\> net start mysql You should try switching your table to InnoDB and make your table ...


0

The tmp tables won't be in /mysql/data, but rather in the subdirectory corresponding to the database. Or maybe in the tmpdir directory. key_buffer_size = 2344M -- that is just for indexes. In a 4GB machine, that does not leave enough room for data. Change it to 700M. thread_cache_size -- 0 is better for Windows. query_cache_size=132M -- too big; drop ...


0

As I said in my comments above your issue comes from a small tmp dir. There are many answers on stackoverflow concerning this. Here is 1 http://stackoverflow.com/a/1067521/716691 And here's how to change mysql tmp dir temporarily without editing my.cnf. Make sure you create a tmp dir somewhere with enough available space. ...


0

Switch to InnoDB to avoid table locks. Table locks can tie up MyISAM tables for a long time. product: INDEX(number_cleaned, manufacturer_id, id) product: INDEX(number_cleaned, source_id, id) number and number_cleaned sound like numeric values, yet you declared them VARCHAR. Be sure to always quote the numbers you compare them against.


0

I would aim for an index on: (number_cleaned, source_id) i.e. KEY ... (number_cleaned, source_id) I never understood why MySQL uses the term key for something that's not, but that's a different story. As @mysql_user points out, investigate whether it's possible to migrate your tables to innodb. If you want to stretch things a bit further you can ...



Top 50 recent answers are included