I am working on a social networking site and am having performance
issues with the search functionality of the site's members.  We are
working with a test database of 3 million member records.

Our MySQL tables look like this:

 member: member_id, username, password

 member_data: member_id, dateofbirth, firstname, lastname, city,
state, zipcode, is_single, is_married, is_divorced, for_friends,
for_dating, for_networking

 member_page: member_id, about_me, like_to_meet, general_interests, music

Members have the ability to search for people based on every field in
the "member_data" table.   For example you can look for 18 to 32 year
olds in California who are Single and are here for Dating or Friends.

The problem is it is slow, really slow.  We have indexed the columns
but they are ineffective and MySQL only uses them in certain cases. 
To compound the problem, we plan on adding at least 10 more columns to
search on, like member's ethnicity and if they drink or smoke.

We have experimented with caching database results but the queries can
take over 5 seconds to run, making non frequently used searches
unbearable.

There has to be a better way that can scale beyond a single server.

---

Request for Question Clarification by rosicrucianpope-ga on 12 Oct 2006 18:11 PDT

Hello nitro2-ga,

I think I can help you with your query optimizations.  I'd like to get
a little more information from you before we proceed.

1)  What version of MySQL are you using, and on what operating system
are you running it?  Windows, Linux?

2)  Do the member_data and member_page tables have unique row IDs of their own?

3)  Is there a one-to-one or one-to-many relationship between member
and member_data, and member and member_page?

4)  Can you post your current query online?

5)  You have three million member records.  How many records are in
the other tables?  Obviously, if these are all one-to-one
relationships, the answer will be three million.  :)

Let's see what we can do for you!

Sincerely,
rosicrucianpope-ga