[SOLVED] increase text search performance

[bcamp1973]

I'm building a AJAX style search into my web app that will operate similar to iTunes where search results auto populate as you type. The way i have it set up now, the AJAX hits the database every keystroke (via PHP) to gather results. Obviously not a problem with a few hundred or even a few thousand records. However, not very scalable. The query searches against 3 fields and is similar to this...

 

SELECT * FROM users WHERE first_name LIKE '%xxx%' OR last_name LIKE '%xxx%' OR email LIKE '%xxx%'

 

...where xxx is the text string passed.

 

Anyway, MySQL recommends using INNODB tables instead of MYISAM. Obviously that would be even slower.  I was thinking of making the user table INNODB and creating a second MYISAM user_cache table where the email, first_name, and last_name values are stored in an aggregate field and search on that instead.  Would this give me better performance or is there a better solution out there? Here's the basic structure I'm thinking...

 

users

-----------

id int(11)

first_name varchar(20)

last_name varchar(30)

email varchar(30)

 

user_cache

-----------

id int(11)

user_id int(11) <- indexed

first_last_email varchar(30) <- indexed

 

[fenway]

There was another topic on full-text searching just a few days ago...

[artacus]

For this type of application w/ large tables, I'd try to avoid searching 3 columns at once. Its important that these run very fast... as fast as you can type. Maybe a radio button that indicates what you are searching on.

 

When I do these, I don't start the search until there are at least 3 characters. For first name, I make them enter a last name and a comma before they can search on it.

[bcamp1973]

Thanks to you both!  I think this is the post fenway was referring too...along with a few other related posts...

 

Full Text Search vs. Standard SQL search

http://www.phpfreaks.com/forums/index.php/topic,129729.0.html

 

full text search

http://www.phpfreaks.com/forums/index.php/topic,114492.0.html

 

MySQL Full Text Searching "Symbols Issue"

http://www.phpfreaks.com/forums/index.php/topic,128232.0.html

 

Full-text search in InnoDB

http://www.phpfreaks.com/forums/index.php/topic,75058.0.html

 

[sOLVED] Need Serious PHP/MYSQL Full-Text Search Help!!

http://www.phpfreaks.com/forums/index.php/topic,127939.0.html

[bcamp1973]

BTW, since innoDB doesn't support FULLTEXT searching, what search option does it support?

[artacus]

LIKE  or RLIKE

[bcamp1973]

so, for performance it looks like i'm stuck with MYISAM.  good to now.  thanks again artacus!

 

[fenway]

Thanks for bringing all of those links here... I'll probably sticky a bunch of them at some point; and usually, you can do have your "main table" as innodb, and search against a "derivative" myisam table which you keep up-to-date with search "stuff".

[bcamp1973]

no problem, thanks for your help. I think i will do that actually. then, if i host this with a provider that has MySQL 5 i'll maybe set a trigger to keep the tables synced.  I've never used triggers before so it's an opportunity to pick up something new