Why are you putting a list of all skills into one field? That's not a very
"relational" method of storing your data and you will have a much harder
time trying to extract skills from a text list of fields than if you used a
very standard database practice called "normalization"
Personally I would create 3 tables: one to contain a list of all people,
one that contains a list of all skills, and a third that will contain an
entry for each skill a person has (using either the primary key or a unique
key from both tables)
CREATE TABLE Person (
ID int auto_increment primary key,
Name varchar(20),
<other columns>
KEY (name)
)
CREATE TABLE Skill (
ID int auto_increment primary key,
SkillName varchar(50)
KEY (SkillName)
)
CREATE TABLE PERSON_SKILL (
Person_ID int,
Skill_ID
PRIMARY KEY(Person_ID, Skill_ID)
)
The primary key on the PERSON_SKILL table prevents the same person from
being assigned the same skill more than once.
Now, you can use text and numerical indices and not full-text indices and
table scans to find your skills list. This is MUCH faster!!!
To get a list of all skills for a person:
SELECT p.Name, s.SkillName
FROM Person p
INNER JOIN PERSON_SKILL ps
ON ps.Person_ID = p.ID
INNER JOIN Skill s
ON s.ID = ps.SkillID
WHERE p.Name = 'somename here'
To get a list of people with a skill:
SELECT p.Name, s.SkillName
FROM Person p
INNER JOIN PERSON_SKILL ps
ON ps.Person_ID = p.ID
INNER JOIN Skill s
ON s.ID = ps.SkillID
WHERE s.SkillName = 'some skill here'
Regards,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Paul Chu"
<[EMAIL PROTECTED] To: "'Paul Chu'" <[EMAIL
PROTECTED]>, <[EMAIL PROTECTED]>
net> cc:
Fax to:
06/18/2004 10:02 Subject: RE: Full Text Index on Large
Tables - Not Answered
PM
Appreciate any help at all
Thanks, Paul
-----Original Message-----
From: Paul Chu [mailto:[EMAIL PROTECTED]
Sent: Friday, June 18, 2004 10:16 AM
To: [EMAIL PROTECTED]
Subject: Full Text Index on Large Tables
Hi,
If I have a table with 100 - 200 million rows and I want to search
For records with specific characteristics.
Ex.
Skills varchar(300)
Skill id's 10 15
Accounting finance etc.
Is it advisable to created a field with skill ids and then use the
Skills column in a full text index
Thanks for your help, Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
