improve MySQL text searches What MySQL settings improve text searches?
Which resources (memory, cpus, disk: ssd) to add first to increase performance?


Setup


Amazon RDS


type: db.t2.medium (2 CPU; 4G mem)
MySQL 5.6.27

Application: Redmine 3.3.0
Response time with simple but very generic search (for example: 'the') with no other load: ~15 sec.
Not using  InnoDB FULLTEXT Indexes
Info on Redmine DB: 
[--] Database: redmine
[--]  +-- TABLE: 81
[--]  +-- COLL : 3 (utf8_general_ci, utf8_unicode_ci, latin1_swedish_ci)
[--]  +-- ROWS : 5114804
[--]  +-- DATA : 1.3G(81.79%)
[--]  +-- INDEX: 288.5M(18.21%)
[--]  +-- TOTAL: 1.5G
[--]  +-- ENGIN : 1 (InnoDB)



MySQL Settings


binlog_cache_size: 32768
binlog_format: MIXED
bulk_insert_buffer_size: 64000000
default_storage_engine: InnoDB
explicit_defaults_for_timestamp: 1
gtid-mode: OFF
innodb_buffer_pool_size: {DBInstanceClassMemory*3/4}
innodb_file_per_table: 1
innodb_flush_method: O_DIRECT
innodb_ft_cache_size: 80000000
innodb_log_buffer_size: 8388608
innodb_log_file_size: 134217728
key_buffer_size: 16777216
local_infile: 1
log_output: TABLE
log_slave_updates: 1
master-info-repository: TABLE
max_binlog_size: 134217728
max_connections: {DBInstanceClassMemory/12582880}
max_heap_table_size: 512000000
performance_schema: 0
query_cache_limit: 512000
query_cache_size: 512000000
query_cache_type: 1
read_buffer_size: 262144
read_rnd_buffer_size: 524288
relay_log_info_repository: TABLE
relay_log_recovery: 1
sync_binlog: 1
table_open_cache_instances: 16
thread_stack: 262144
tmp_table_size: 512000000
query_cache_size: 512000000 plus query_cache_type: 1 is really bad for performance.

If the QC is on, then every modification to a table will cause a purge of all rows for that table in the QC.  The bigger the QC is, the longer it takes.  I recommend only 50M for the size.  Or turn the QC off.  All

"the" is a poor test case since it is (assuming English) so very common. Pick some word that occurs only a few times.  Or, maybe a word that occurs once, a word that occurs about 10 times, and a word that occurs 100 times.

max_heap_table_size: 512000000 and tmp_table_size: 512000000 are also bad because they are 12% of RAM; lower to 1% of RAM.  In complex queries, those limits come into play.  If there are several complex queries running at once, you could blow out RAM.  Swapping is really bad for performance.

If you can't permanently change those settings, complain to the provider.  If they won't listen, then change providers.  That's how bad I think the settings are.  No change to "memory, cpus, disk: ssd" can fix the query_cache_size problem.