Geek asks and answers

Mysql query is too long

I am new to advanced queries, so I probably have something fundamentally wrong, because when there are more than 1 million records in the database, I get this response from my request ...

ERROR 2013: Lost connection to MySQL server during query

Yes! It actually takes so much time that it gets confused before it ends.

My request is ...

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 sample data (with multiple entries) at http://sqlfiddle.com/#!2/cbbda/28

The sample in sqlfiddle runs quickly because there are only a few records, but I tried to duplicate the records on my own server, and the query started quickly with a few records and very slow after I added a million records.

Is there a way to get quick results?

+5
source share
6

, ... ... ... 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 . , ... , , !

... IN BOOLEAN MODE, 2 , http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html?

+1

- , , , LIKE . , , .

, abc_like_yellow abc_like_spork .., , .

, .

0

username, , .

, , ? .

, , , (.. ).

0

, , , , ...

(SELECT username, MATCH(field_abc,field_def) AGAINST ("spork yellow" IN BOOLEAN MODE) AS score FROM table_1 HAVING score>0)
UNION ALL
(SELECT username, MATCH(field_ghi,field_jkl) AGAINST ("spork yellow" IN BOOLEAN MODE) AS score FROM table_2 HAVING score >0)

, GROUP BY, PHP :

while($row = mysql_fetch_array($result) )
{
    if( in_array($row['username'],$usernames) )
    {
        $usernames_count[$row['username']] += $row['score'];
    }
    else
    {
        array_push($usernames,$row['username']);
        $usernames_count[$row['username']]=$row['score'];
    }
}
arsort($usernames_count); // Sort the results high->low

foreach($usernames_count as $key=>$value)
{
    echo "Username: ".$key." had a score of ".$value." in the search results<br/>";
}

, .

0

, , .

, , CloudFlare

SQL, Google Cloud SQL . Google SQL, , Google.

, , .

, !

VCNinc
0

SQL Server, SQL- + L

. ;

, , , .   , "".   , .

In your example, the complex last set of orders is very expensive.

Rather, do the following: Pull the main information into a temporary table, with 9 additional columns (int type, intely set to 0) after filling in the main data, update each of 8 columns based on criteria 0 or 1, update the last column as the sum of the remaining 8 columns to retrieve information from the table using only one “order” based on column 9.

In my experience, this approach takes only 20% of the time compared to completing an order at your own discretion.

0
source

More articles:


All Articles