On lun, 2003-02-10 at 23:28, Jan Hidders wrote:
SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex WHERE cur_id=si_page AND (MATCH (si_text) AGAINST ('math characters') AND (cur_is_redirect=0) ) AND (cur_namespace=0) LIMIT 0, 20;
This query looks extremely suspect to me because it seems to combine the worst of two worlds: the locking overhead of InnoDB tables (for table 'cur') and large locking granulatity of MyISAM tables (for table 'searchindex' which needs to be updated every time an article is saved) .
Last week I tried removing the join and doing a second query for the 'cur' info using just the results from 'searchindex'; as I recall it wasn't helping much if at all. A small difference might stand out more now, though.
As I said before, I really don't think we should be using InnoDB tables, but perhaps Ian has some good arguments why we should.
Best reason I've found is that the table doesn't have to lock the whole time during backup dumps; the dump gets a consistent read without blocking inserts and updates.
Of course, this could be got around by having a replicated slave database and tacking backups from that.
-- brion vibber (brion @ pobox.com)