hello,
i have a table as follows:
Members (
teamID int(6) unsigned default '0',
ID int(11) unsigned NOT NULL auto_increment,
name varchar(30) default NULL,
number smallint(5) unsigned default NULL,
height tinyint(3) unsigned default NULL,
weight tinyint(3) unsigned default NULL,
DOB varchar(21) default NULL,
Hometown varchar(30) default NULL,
yearsPlaying tinyint(3) unsigned default NULL,
gamesPlayed tinyint(3) unsigned default NULL,
Points smallint(6) unsigned default NULL,
email varchar(60) default NULL,
retired tinyint(3) unsigned default NULL,
detailsID int(11) unsigned default NULL,
PRIMARY KEY ( ID ),
KEY teamID ( teamID )
i am trying to determine which will make this table work faster in my web
applications.
if i keep detailsID as a tinyint that refers to a new table of the structure:
memberDetails (
ID int(11) unsigned NOT NULL auto_increment,
details blob,
PRIMARY KEY ( ID ),
OR if i alter detailsID int(11) to details blob,
i'm not interested so much in normalization, but optimization where i may
be searching for records regularly based on teamID, ID, name, number, and
name AND number.
i doubt i'll ever need to search the details records.
but with so many criteria for indexing - i'm wondering if having the blob
in the main table will slow things down.
this table is my primary table in a very hi volume website, and should
expect some 3000 - 5000 hits per day.
i've looked thru table optimization in the manual, but i'm not confident
with how mysql sets up its searches based on indexes. and find that it
doesn't always use the indexes i create to assist in the searches.
i'm designing for around 15,000 records in this table. where details can be
several lines of text (on average 150 words).
ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
