Alright, wanna help me tackle the next problem?

The search is slow. That's fine, I don't really mind it being slow. I'm using FullText indexes to help speed it up.

But, what is a problem is that while any ONE user is performing a search, it locks other tables (such as the users table), bringing the site to its knees for everyone.

Most of these tables are MyISAM. How do you feel about me switching them to InnoDB? What are the realistic pros and cons of InnoDB vs. MyISAM? I know that Inno is slower to do full table counts/etc, but is it better for tables that are accessed and updated constantly?

-av

Posted by Avatar-X Most of these tables are MyISAM. How do you feel about me switching them to InnoDB? What are the realistic pros and cons of InnoDB vs. MyISAM? I know that Inno is slower to do full table counts/etc, but is it better for tables that are accessed and updated constantly?

It's sort of a wash for full text searching on tables which are being constantly updated. InnoDB doesn't have full text indexing, but it also doesn't do a giant table level lock for things like MyISAM does.
http://www.kavoir....pros-and-cons.html

-D

Right, I get that. What I am wondering is, can I switch the USERS table to InnoDB? I don't need full-text searching on that table.

-av

Posted by Avatar-X Right, I get that. What I am wondering is, can I switch the USERS table to InnoDB? I don't need full-text searching on that table. -av

Then yeah, probably, and it should be better if it's being constantly updated and read.

I have no suggestions regarding performance, but functionality could sure be improved when it comes to short search strings.

As things are, when a user searches for a short text string (I don't know the lower length limit, but here's a sample search for "sex") the results page comes up empty. This is misleading. If you're not indexing short terms you should come right out and admit it on the results page. Better yet, do it on the search page too.

Also, it couldn't hurt to echo the search terms on the results page.

More thread necromancy, but could you switch to dirty read? You might miss a very recent post if the write happened while you were searching, but it'll show up on the next search. More importantly, you won't lock the table.