Since MySQL stores RowIDs with the indexed words
instead of the table's primary key IDs, and since it
uses only one index per table in any query, performing
a full-text search on a large table (several million
rows) and joining it with another large table proves
to be extremely slow!
The bottleneck seems to be the disk, as MySQL
generates an insane number of disk reads while trying
to join the two tables without using common indexes
between the two.
Basically I have two tables, A and B. These tables
cannot be merged, so there's no way around using a
join. One of these tables, A, has a TEXT column that
is full-text indexed. Now these tables are normally
joined using the indexes on their primary keys. But if
use a full-text criteria in the WHERE clause against
A, then MySQL will NOT use the primary key index on A
to perform the join. It instead seems to get the
result from the full-text index and uses it to perform
a huge number of random (unsorted) lookups on the
second table to perform the join.
I tried to perform a separate query on each table and
store the results (primary key IDs) in a couple of
temp tables, then join these tables manually. But
these tables can be huge, and doing this for each web
paging request is very inefficient. In addition,
limiting the size of these temp tables would render
the search dysfunctional because it won't be
encompassing any more.
With non-full-text indexes, you can just merge the
indexes into a composite index to work around the
single-index-per-table limitation. But you can't merge
a regular primary key index and a full-text index into
a composite index, and I have no idea why MySQL
developers didn't just use the primary key IDs with
the indexed words, as in MS SQL Server full-text
index, instead of the physical row IDs. May be it's a
MyISAM limitation that an index, no matter what type
it is, has to use physical row IDs as table pointers
instead of primary keys.
Is there any way around this limitation? I'd like to
know if someone has been in a such a situation and how
he or she solved it.
I appreciate your help!
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
