Design Issue with a large Table

[RealM]

I have about 50 fields in the table that I'm needing to make searchable. In this I have noticed that when you index the table. You can use multiple columns for the index but with a Key length of 500 Characters. Is there a Way I can get around this ? Or is there a better way to design the table?

 

[sleipnir214]

I'd bet you need to go to a better database schema.

What are you storing in these 50 fields? Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[RealM]

I'm storing most of the users data. How I have it setup now, is below.

create table userinfo_self (
ID int(9) DEFAULT '0' NOT NULL AUTO_INCREMENT,
uid varchar(50) BINARY,
password text,
active varchar(4),
gender varchar(6),
age int(3),
dob varchar(20),
ast_sign varchar(50),
city varchar(65),
c_city1 varchar(65),
c_city2 varchar(65),
state varchar(2),
zipcode int(6),
country varchar(50),
marital_status varchar(50),
ethnic varchar(50),
religion varchar(50),
kids varchar(50),
smoking_habbit varchar(50),
drinking_habbit varchar(50),
facial_hair varchar(50),
eye_color varchar(50),
hair_color varchar(50),
height varchar(50),
weight varchar(50),
body_type varchar(50),
education varchar(50),
occupation_status varchar(50),
profession varchar(50),
income_level varchar(50),
interests text,
entertainment text,
activities text,
music text,
desc_yourself text,
relationship varchar(50),
picpath1 text,
thumbpath1 text,
picpath2 text,
thumbpath2 text,
picpath3 text,
thumbpath3 text,
picpath4 text,
thumbpath4 text,
picpath5 text,
thumbpath5 text,
PRIMARY KEY (ID),
UNIQUE id (ID),
FULLTEXT INDEX (activities,music,interests,entertainment,desc_yourself),
index qsearch (active,country,age,zipcode,gender,state,city,c_city1,c_city2),
index uid (uid),
index active (active),
index gender (gender),
index age (age)
);


There is only one table that I'm using for the search feature and it is above. Let me know if you have any ideas.

 

[sleipnir214]

Man, you sure do like varchar, don't you?

I recommend that you use the appropriate columntype for the type of data you're going to store.

Here's your table structure, with my comments in italics


ID int(9), should be int unsigned
uid varchar(50) BINARY,
password text,
active varchar(4), should be columntype set('Y','N
gender varchar(6), should be columntype set('M','F')
age int(3),
dob varchar(20), should be columntype date
ast_sign varchar(50),
city varchar(65),
c_city1 varchar(65),
c_city2 varchar(65),
state varchar(2),
zipcode int(6), should be columtype varchar(10) to accommodate
U.S. ZIP+4 zipcodes ("12345-6789&quot
country varchar(50),
marital_status varchar(50), should be set ('Married', 'Single', 'Divorced'...)
ethnic varchar(50), could be either a set or a lookup in another table
religion varchar(50), should be a lookup in another table
kids varchar(50), if this is the names of the kids, those kids should
stored in another table and related to this one
smoking_habbit varchar(50),
drinking_habbit varchar(50),
facial_hair varchar(50), probably could be a set
eye_color varchar(50), should be a set('blue','green'...)
hair_color varchar(50), should be a set('blonde','brown','black'...)
height varchar(50), store this as a tinyint unsigned in inches or smallint in cm
weight varchar(50), store as smallint in pounds or tinyint in kilos
body_type varchar(50), could this be a set?
education varchar(50), set
occupation_status varchar(50), set
profession varchar(50), lookup from a table of professions
income_level varchar(50), set
interests text, should be a many-to-many relationship through a imtermediate table
to a table of interests
entertainment text, should be a many-to-many relationship through a imtermediate table
to a table of entertainment
activities text, should be a many-to-many relationship through a imtermediate table
to a table of activities
music text, should be a many-to-many relationship through a imtermediate table
to a table of music types
desc_yourself text,
relationship varchar(50),
picpath1 text, probably varchar(150) would be enough
thumbpath1 text, probably varchar(150) would be enough
picpath2 text, probably varchar(150) would be enough
thumbpath2 text, probably varchar(150) would be enough
picpath3 text, probably varchar(150) would be enough
thumbpath3 text, probably varchar(150) would be enough
picpath4 text, probably varchar(150) would be enough
thumbpath4 text, probably varchar(150) would be enough
picpath5 text, probably varchar(150) would be enough
thumbpath5 text, probably varchar(150) would be enough
Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!