Imagine the following situation:
Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index.
FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4
Table B uses InnoDB and contains some other fields, where 5 of them are indexed as 'ORD'.
ORD order1 | order2 | order3 | order4 | order5
I like to do a full-text-search on tableA by joining tableB on it's foreign id and ordering the results by the indexed columns of tableB.
Query1 - match all 4 fulltext-columns:
SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id`
WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`, `tableA`.`fulltext4`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
)
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15
takes 1.6565 seconds.
EXPLAIN Query1:
select_type table type possible_keys key key_len ref rows Extra
SIMPLE tableA fulltext PRIMARY,FULLTEXT FULLTEXT 0 1 Using where; Using temporary; Using filesort
SIMPLE tableB eq_ref PRIMARY PRIMARY 4 db.tableA.tableB_id 1
no index is used, temporary table was necessary. I don't know what "Extra 1" in second row means.
Query2 - match only 3 columns:
SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id`
WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
)
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15
takes 0.0114 seconds.
EXPLAIN Query2:
select_type table type possible_keys key key_len ref rows Extra
SIMPLE tableB index PRIMARY ORD 783 NULL 15 Using index
SIMPLE tableA eq_ref PRIMARY PRIMARY 4 db.tableB.id 1 Using where
The order of the listed tables has changed. For tableB, the index was used, for tableA no temporary table was necessary.
It doesn't matter which fulltext-column I exclude from the query - as long as I match less than 4 of them, I get the duration and explanation of the showed Query2.
Maybe interesting: the amount of rows is equal in both tables, about 180k.
I'd like to know the reason why this behaviour occurs. I mean, it seems like the whole way of excecution would depend on the number of matched columns.
edit: now I'm completely confused. I deleted the fulltext-index. I matched all 4 (ex-)fulltext-columns (Query1). Now it takes 0.1205 seconds. EXPLAIN shows me that no temporary table is needed; but I wonder how I can match faster in Boolean Mode without having a fulltext-index.