mysql query taking too long

Question

I am new to advanced queries so I likely have something conceptually wrong because when the database has over 1 million records I get this response rom my query...

ERROR 2013: Lost connection to MySQL server during query

Yes! It actually takes so long that it pukes before it finishes.

My query is this...

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
    table_1.field_abc REGEXP "(spork|yellow)" OR
    table_1.field_def REGEXP "(spork|yellow)" OR
    table_2.field_ghi REGEXP "(spork|yellow)" OR
    table_2.field_jkl REGEXP "(spork|yellow)"
GROUP BY users.username
ORDER BY
(
    ( CASE WHEN table_1.field_abc LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_1.field_abc LIKE "%yellow%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_1.field_def LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_1.field_def LIKE "%yellow%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_ghi LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_ghi LIKE "%yellow%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_jkl LIKE "%spork%" THEN 1 ELSE 0 END ) +
    ( CASE WHEN table_2.field_jkl LIKE "%yellow%" THEN 1 ELSE 0 END )
)DESC;

I posted a sample dataset (with only a few records) at http://sqlfiddle.com/#!2/cbbda/28

The sample at sqlfiddle runs quick because there are only a few records but I tried duplicating records on my own server and the query ran quick with only a few records and extremely slow after I added a million records.

Is there any possible way to get my results quick?

Answer 1

Well folks... With your help we have a solution... See... http://sqlfiddle.com/#!2/fcfbd/5 BUT I DO STILL HAVE A QUESTION...

I altered the table to add the indexes...

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` );

I then took the advice of @Barmar and changed the code to this...

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;

With over 1,000,000 records in my real database, I got my result in 6.5027 seconds. That is A LOT better than... well, taking so long that it puked!

My only question now is... Why does it only work with IN BOOLEAN MODE and not the other 2 options mentioned at http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match or http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html?