Since you are already splitting your data into "fulltext columns" and
"other data" I would keep that design. Others on the list have greatly
improved their performance by running their tables in this same fashion.
If by some chance your "non-fulltext" columns are all fixed width, there
is another speed boost just by changing your design.
I think you are on the right track to an optimal storage solution.
However, I would not duplicate all of your fields in your "fulltext"
table, only the Primary key and the actual FT data needs to be there. The
rest of the data is already on your non-FT table so no need to double up
on your storage.
Of course, I could suggest more concrete examples if you actually post the
table designs you have now (SHOW CREATE TABLE tablename\G) so that I could
refer to your tables by their real names and columns.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Aleksandr Guidrevitch <[EMAIL PROTECTED]> wrote on 12/22/2004 03:47:39 PM:
> Hi there,
>
> Is there any common strategy (of using indexes)
> to avoid filesorts while sorting searches by a field ?
>
> Eg. I want fulltext search, then sort results by
> some other table field.
>
> For example .:
> create table article (
> id int not null primary key auto_increment,
>
> sort1 int not null,
> sort2 int not null,
>
> description text not null,
>
> FULLTEXT description (description)
> )
>
> explain select id from article where match (description) against
> ('cccc'in boolean mode) order by sort1;
> mysql> mysql> explain select id from article where match (description)
> against ('cccc' in boolean mode) order by sort1;
> +----+-------------+---------+----------+---------------
> +-------------+---------+------+------+-----------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+---------+----------+---------------
> +-------------+---------+------+------+-----------------------------+
> | 1 | SIMPLE | article | fulltext | description | description
> | 0 | | 1 | Using where; Using filesort |
> +----+-------------+---------+----------+---------------
> +-------------+---------+------+------+-----------------------------+
>
>
> It doesn't matter whether I have index on sort1, filesort is always
used.
> Furthermore, I've noted that adding an index slows the query down
> with each index added (suppose becuase of table index becomes bigger).
>
> I come to a solution where I create a separate table (exact copy) with
only
> fulltext indexes, then query it with order by LEFT JOIN with original
table,
> eg:
> SELECT article.id, article.description FROM search
> LEFT join article
> ON search.id = article.id
> WHERE
> MATCH (description) AGAINST ('some' IN BOOLEAN MODE)
> ORDER BY search.sort1
> LIMIT 0, 50;
>
> Also, I intensively use table article for listing (w/o search)
> items sorted by sort1 or sort2 fields, so in article table
> indexes for sort1 and sort2 are absolutely necessary.
>
> So currently I see only 2 solutions
> 1. add to the article table ndescription
> field (with stemmed description), and fulltext index on it
>
> 2. create a separate table for this purpose.
>
> I hope there is a 3rd variant, a good idea on how to
> avoid filesort
>
>
> Sincerely,
> Aleksandr
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
