Theoretical Performance Details of FAD vs. FAC (WAS Re: Stalled custom field development)

[Max Kanat-Alexander]

On Wed, 2005-03-23 at 02:23 -0800, Maxwell Kanat-Alexander wrote:
> 	Yeah, I think FAC would be slightly faster, although I haven't verified
> the testing methods of the two testers.

	OK, I've downloaded Myk's tests and run them myself, with a few (very
minor, not test-impacting) modifications on MySQL 4.1.10a on my idle
dual-Celeron box.

	The numbers aren't nearly as important as the EXPLAIN output.

	There is a problem with FAD, but we only run into it with text fields
that require FULLTEXT indexes.

	For example, take the following SQL statement searching for the string
'meta' in a plain-text custom field, using FAD and a fulltext search:

-------------------------
SELECT bug_id FROM distcol_str
 WHERE field_id = 0 
       AND MATCH(value) AGAINST ('*meta*' IN BOOLEAN MODE);
-------------------------

	(We don't care about fields that don't use FULLTEXT indexes or that
can't use MATCH -- they don't have this problem.)

	Obviously, a fulltext index helps us here on the "value" field, which
is a mediumtext.

	However, MySQL will only use ONE INDEX. And guess what -- in this case,
it picks the FULLTEXT index, not the field_id index. If we use a LIKE
instead of a match, it picks the field_id index (because MySQL is unable
to use a FULLTEXT index for a LIKE '%string%', so that's just a MySQL
problem).

	With a fulltext index, we can't create a multi-column index, so we
can't "work around" this problem.

	So with FAD, FULLTEXT fields need their own table, so that we don't
have to ever do a search on "field_id".

	Other comments on the performance testing suite coming up.

	-Max