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.)