Increase performance WITHOUT adding indexes

Question

I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't.

Obviously I'm using indexes for the most common queries on this table; but I always hesitated to add indexes for queries which are only performed every few seconds or even minutes. They are just a minority compared to the more important queries.

As far as I know, adding more indexes makes inserting new rows and updating existing data slower (and UPDATE/DELETE/INSERT queries happen very frequently for this table); but adding indexes also makes normal SELECT queries slower, since MySQL has to find the perfect index first and has to check more index possibilities. Obviously, the slightly slower SELECT statements wouldn't be a problem; but I'm more worried about the UPDATE queries.
But on the other hand: I don't want these rare queries to take 1+ seconds, either.

Is there anything else I could do?
These performance heavy queries are creating temp tables and I thought about increasing the tmp_table_size (it's currently 16MB and I'm having 6+GB of unused RAM space). But then I read that this makes things even worse.

Join/Sort buffer sizes are already at 128/256MB
(Now that I think about it: These two variables are lower than the table size itself. That might be a problem; though I don't want to increase variables without knowing exactly what I'm doing.)

Answer 1

I wrote some posts about RAM disk and temp tables

• Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
• Jan 17, 2012 : How can I optimize my mysql setup to create my index faster?
• Sep 23, 2011 : skip copying to tmp table on disk mysql

The basic idea would be to set set tmp_table_size and max_heap_table_size to the bare minimum value so that move temp tables to the RAM disk quickly.

This would only help if your queries produce temp tables and you do not wish to tune tables and indexes. However, it is imperative to tuning queries and use indexes to produce the smallest possible temp tables before tuning the tmp_table_size and max_heap_table_size options or even go the route of the RAM Disk.

Nevertheless, since you have 16GB on the server, you could set up good 2G RAM disk, configure good settings for join, sort and read buffers per connection, and sufficient RAM left for the OS.

UPDATE 2014-11-25 11:09 EST

Your last comment

I'm wondering what's the difference between temp tables on a RAM Disk and temp tables in "memory"? I think that's the same, isn't it? Therefore it makes no difference wheter I set tmp_table_size & max_heap_table_size to a high or a low value - RAM is used in both situations. Or am I wrong here?

It makes a big difference. Why ?

• If you set tmp_table_size/max_heap_table_size too high, each DB Connection will needlessly glut memory (This is in addition to other per-connection buffers : return my.conf values to sane levels (total memory footprint = 220GB)) I have seen customers set these to 100G to avoid thinking about this issue (Bad decision).
• If you set tmp_table_size/max_heap_table_size high and queries need just a little more here is what has to happen:
  • You have tmp_table_size/max_heap_table_size set at 64M
  • You makes a temp table that takes up more that 64M (let's say 66M)
  • Temp table reaches 64M
  • Query suspends processing
  • Temp table is migrated to disk
  • Last 2M needed is loaded into the temp table on Disk
  • Query continues processing
  • This is counted in Created_tmp_disk_tables
• HERE IS MY POINT : If you set tmp_table_size/max_heap_table_size low on purpose (let's sa the minimum value of 1K), the temp table will materialize in the RAM Disk faster because it only has to stop and transfer 1K from RAM to Disk.