• MySQL知识库 :: performance
  • Why is a SELECT that uses CONCAT() so slow?

  • Discussion

    When running queries on a large table and using a function like CONCAT() in the WHERE clause, the queries can be much slower than when the function is not used. This is usually caused when the function is used on an indexed column.

    As an example, suppose a table contains records on people and has a column for each person's first name and last name. Suppose further that these name columns are indexed. Based on these suppositions, the following SQL statement would be slower as a result of the CONCAT() function in the WHERE clause:

    SELECT id, CONCAT(name_first, ' ', name_last), city 
    FROM customers 
    WHERE CONCAT(name_first, ' ', name_last) = 'John Smith';

    If you process an indexed column with a function in a WHERE clause, it cannot be used as an index to quickly locate matching rows. The query function in the WHERE clause in the example above causes a full table scan to be performed. MySQL will execute CONCAT() on the name columns for every single row to make the comparison to the customer name 'John Smith'. To be able to use the indexed columns (the name columns), something like the following should be entered instead:

    SELECT id, CONCAT(name_first, ' ', name_last), city 
    FROM customers 
    WHERE name_first = 'John ' AND name_last = 'Smith';