Re: Fulltext optimization

[James Day]

MySQL will only use one index for each table name/alias in a given select statement. One way to work around this is with a self join, so you can use the fulltext index with "table as table_ft" and the date index with "table as table_date". Each of the aliases gets to use one index. You'll also need a where condition to link the records by primary key value, where "table_ft.primarykey = table_date.primarykey".

If you know the date ranges in advance or they are always a subset of your data, you may find it helpful to search against a table containing only a copy of the data for the date range required. If you get many identical searches and update this periodically instead of continuously this approach can also let the query cache work. Each update removes all cached queries involving that table from the query cache, so batching changes increases the chance of hits before the saved queries are purged by an update.

If you're contemplating doing a new query for each page you might consider getting several pages worth and having the next page button submit the primary key values of the remaining pages in the set, to avoid making another search. If you do take this approach, consider adding a hash made in part with a secret key or other checking mechanism before you rely on the data coming from the client, since a malicious user could submit arbitrary data. Alternatively, saving several pages of outstanding query results in a heap table and expiring saved queries from it regularly might be useful.

I'm not a MySQL employee.