Google Answers Logo
View Question
Q: Database Structure for Search ( No Answer,   0 Comments )
Subject: Database Structure for Search
Category: Computers > Programming
Asked by: nitro2-ga
List Price: $40.00
Posted: 12 Oct 2006 17:45 PDT
Expires: 12 Oct 2006 23:43 PDT
Question ID: 773062
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

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!

There is no answer at this time.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy