Wow, thanks for your advices, they help me a lot and give me more confidence to keep using PW. I just forgot about indexing and caching, they're wide matter but really helpful in scalability indeed. I heard someone said that MySQL would run efficiently with optimized single queries rather than complex queries.
About the searching in PW, I think there are a lot of dedicated full text search servers like Sphinx, Solr, Lucence, Xapian that are feature-rich and more powerful than MySQL native search functionality. I used to use Sphinx to replace Wordpress built-in search and it worked perfectly, very fast and very less memory consumption. I can say it's a must for every database-driven website.
A little question, is there any module/plugin support integrating one of those search engines in PW yet??