Very slow table with fulltext search required - HELP

ppowell

I have a table that contains indexes and fulltext indexes, using MySQL 3.23.41 and MyISAM type tables.

Client requirement is that the table be searchable AND any insert into the table be fast.

The table is searchable, however, it is very VERY slow due to the fact that it has fulltext searches. I have an index on the fields referenced in the 'ORDER BY' clause along with the fulltext that is referencing the fields referenced in other queries with MATCH() option for searching.

How can I create a table that is optimal that contains both indexes and fulltext search? In short, how can I have my cake and eat it too?

Thanx
Phil

nemonoman

ideally, you'd create a transaction table and a reporting table, updating the reporting table with a cronjob that runs independently in the background. You could run it fairly regularly (i.e., once a minute), and since you're only inserting new transaction rows in the reporting table it should be unobtrusive and relatively fast.

Back when I was an oracle dweeb and computers were slow and stupid, speedy, lightly indexed transaction tables coupled with independently updated, heavily indexed reporting tables were considered to be best practice.

ppowell

Could you translate all of that to Non-DBA talk, please? Or rather, Non-DBA-That-Uses-MySQL-More-Than-Oracle talk?

Thanx
Phil

nemonoman

MyTransactionTable

All your fields, Minimal indices

Do all your inserts and updates here

MyReportingTable

All your fields, FULLTEXT index

Do all your searching here

Cron job

mysql_query("INSERT INTO MyReportingTable SELECT * FROM MyTransactionTable WHERE timestamp>'$lastTimeIranTheCronJob'");

ppowell

Ahh ok THAT I understand.. I was thinking about doing just that.

For now we still have the one table with the one index that maps to the "ORDER BY" default field clause, and the FULLTEXT mapped to what is in MATCH(), but breaking them apart like that would make sense, but to make sure, the data in both tables would be mirrored, only the indices would be different and each table used differently.

Like for listing content I would use the TransactionTable, for searching I would use the ReportingTable, but for inserting I would use the TransactionTable while running a CRON that would mirror the data from Transaction to Reporting on the back end, right?

Phil

ppowell

Originally posted by nemonoman
Cron job

mysql_query("INSERT INTO MyReportingTable SELECT * FROM MyTransactionTable WHERE timestamp>'$lastTimeIranTheCronJob'");

Big problem though.. in MySQL I don't think you can use that syntax, right? INSERT INTO.. SELECT.. you can't do subqueries in MySQL or is that not considered a subquery?

Phil

nemonoman

I use it all the time:

http://www.mysql.com/doc/en/INSERT_SELECT.html