Re: Seeking advice on setup

[Rick James]

FULLTEXT sounds like a requirement of the application. That implies MyISAM, not InnoDB.

200 rows seems like a lot for the user to get.

key_buffer_size = 1G
is about the only tunable that is worth using, and that might be a good value for your application.

At 1.6GB, 200 rows should not be a performance problem. At 16GB, there will be a lot more disk I/O. (Also, when you start up the server, there will be a few hundred reads because nothing will be cached. -- That is, the first query will take perhaps 5 seconds.)

You need to use "IN BOOLEAN MODE"! Without that, it will check all the rows.

Let's dissect how it works (assuming BOOLEAN MODE).

1. Look up the word(s) given -- This involves drilling down BTree indexes in the FULLTEXT index. Caching will be moderately good, since words tend to be reused. This yields some number of pointers to the data -- a few if words are rare; hundreds for common words.

2. Look up each row. This may be the costly part. N rows means N lookups. No, the "LIMIT 200" will not limit this step. At 1.6GB, the data will tend to get cached in RAM and stay 'fast'. At 16GB, most of the N fetches will be to disk. A rule of thumb: 100 disk hits per second. So if a query finds 600 articles, that's nearly 6 seconds.

3. Sort by date -- trivial amount of time

4. Deliver 200 -- trivial.

If someone searches for a word that occurs in 49% of the articles, it will be painfully slow (at 16GB). If they search for 2 such words, it will be faster. Step 1 won't be bad; Step 2 will use the AND of the two words, thereby hitting only a few % of the articles.