MYSQL performance issue concat in select or in where

I used to develop databases under ms-sql, and now I've moved to mysql. Great progress. The problem is that I don't have any tool to see graphically the query execution plan... EXPLAIN doesn't really help.

Thus I require your advice on this one:

I'll have a table with approximatively 50000 entries: The two following queries are giving me the same result but I need to know which one will be the more efficient/quick on a huge database. In the first one the concat is in the where, whereas in the second one it is in the select with a having clause.

SELECT idPatient, l开发者_如何学运维astName, firstName, idCardNumber 
FROM optical.patient 
WHERE CONCAT(lastName,' ',firstName) LIKE "x%"; 


SELECT idPatient, CONCAT(lastName,' ',firstName) as formattedName, idCardNumber 
FROM optical.patient 
HAVING formattedName LIKE "x%"; 

Thanks in advance for your answers.

In both versions, the query cannot use index to resolve WHERE and will perform full-table scan. However, they are equialent to:

SELECT idPatient, CONCAT(lastName,' ',firstName) as formattedName, idCardNumber 
FROM optical.patient 
WHERE lastName LIKE "x%"; 

And it can use index on lastName

If you need to search by any of the 2 fields, use union

SELECT idPatient, CONCAT(lastName,' ',firstName) as formattedName, idCardNumber 
FROM optical.patient 
WHERE firstName LIKE "x%"; 
UNION 
SELECT idPatient, CONCAT(lastName,' ',firstName) as formattedName, idCardNumber 
FROM optical.patient 
WHERE lastName LIKE "x%"; 

I don't believe you'll see any difference between these two queries since the CONCAT needs to be executed for all rows in both cases. I would consider storing formattedName in the database as well, since you can then add an index on it. This is probably the best you can do to optimize the query.

As an aside, you may find pt-visual-explain to help with visualizing EXPLAIN output.