Xavier LENOIR wrote:
>>I use queries like this :
>>SELECT count(*) FROM MyTable WHERE (MATCH(Title) AGAINST('a word'));
>>SELECT * FROM MyTable WHERE (MATCH(Title) AGAINST('a word')) LIMIT 10;
>>
>
>
Have you tried building a seperate table of titles for the sake of
having a smaller table to scan through?
SELECT * FROM MyTable LEFT JOIN Titles ON TitleID = Titles.ID WHERE
(MATCH (Titles.Name) AGAINST ('a word')) LIMIT 10;
... I've found this type of optimization helps on large (disk-size)
tables. It helps even more if you make the table like:
CREATE TABLE Titles (
ID int unsigned not null auto_increment,
Name CHAR(150),
PRIMARY KEY (ID),
FULLTEXT (Name),
INDEX NameIdx(Name)
);
(Try both types of index to see if either is faster ... )
Also try putting your 'Autx' fields in a seperate table, for the sake of
good design, then create a cross-linked table to join it to the main table.
tables
------
MyTable: ID, TitleID, details. { PRIMARY KEY (ID) }
Titles: ID, Name { PRIMARY KEY {ID) }
Auts: ID, Data { PRIMARY KEY (ID) }
PS, would you believe this message never contained SQL or QUERY?
MovieAuts: MovieID, AutID { PRIMARY KEY MovieAutIDX (MovieID, AutID);
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
---------------------------------------------------------------------
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
