mysql查询耗时_mysql查询耗时太长

好伙计……在你的帮助下我们有一个解决方案……见……

http://sqlfiddle.com/#!2/fcfbd/5

但我仍然有一个问题……

我改变了表来添加索引……

ALTER TABLE `users` ADD FULLTEXT ( `username` );

ALTER TABLE `table_1` ADD FULLTEXT ( `field_abc`,`field_def` );

ALTER TABLE `table_2` ADD FULLTEXT ( `field_ghi`,`field_jkl` );

然后我接受了@Barmar的建议并将代码改为此…

SELECT users.username,

table_1.field_abc, table_1.field_def,

table_2.field_ghi, table_2.field_jkl

FROM users

LEFT JOIN table_1 ON table_1.username = users.username

LEFT JOIN table_2 ON table_2.username = users.username

WHERE

MATCH(table_1.field_abc,table_1.field_def,table_2.field_ghi,table_2.field_jkl)

AGAINST ("spork yellow" IN BOOLEAN MODE)

GROUP BY users.username

ORDER BY

(

( CASE WHEN MATCH(table_1.field_abc) AGAINST ("spork" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_1.field_abc) AGAINST ("yellow" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_1.field_def) AGAINST ("spork" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_1.field_def) AGAINST ("yellow" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_2.field_ghi) AGAINST ("spork" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_2.field_ghi) AGAINST ("yellow" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_2.field_ghi) AGAINST ("spork" IN BOOLEAN MODE) THEN 1 ELSE 0 END ) +

( CASE WHEN MATCH(table_2.field_ghi) AGAINST ("yellow" IN BOOLEAN MODE) THEN 1 ELSE 0 END )

)DESC;

在我的真实数据库中有超过1,000,000条记录,我的结果是6.5027秒.那比A …好多了,花了这么长时间才发现它！