[eluser]danoph[/eluser]
As you guys said, MyISAM and MySQL are built to handle a lot more than 3 million records. And, every php/mysql/ruby on rails tutorial, book, blog article, and class teaches using 3 tables for polymorphic associations (items, tags, taggings aka item_tags here). I've never seen it done another way, and you always end up with redundant data if you don't use three tables. Plus, I would think using integers would be the fastest route to approach the problem rather than using full text, since the integers can be indexed for one, and the data is a lot smaller? I'm no expert though, and I can't find any books or resources on the internet about this. Really frustrating!
While experimenting with the table indexes and mysql server settings, I was able to get the query I was using from 1.3 seconds down to around .4, but I think there is still room for improvement. When the site keeps growing, that number will just keep getting bigger. I am trying to brainstorm of a way to use PHP to help out or my sphinx search engine instead since its' queries are super fast...this is a huge challenge and I am still baffled there aren't sites on the internet that have this problem solved already!
Hmm...facebook and digg use cassandra? I may have to look into that...I know facebook uses polymorphic associations by "recommending" friends to you. Maybe that would be a good route to go. Mongo looks interesting also. I'll have to spend a few hours looking at these now
Thanks!
