compare two mysql sql performance?

Question

select * from goods where (name like '%%' or brand '%%' or alias like '%%') and category_id = 1 order by id limit 20

select * from goods where category_id = 1 order by id limit 20;

Mysql version 5.6.16-log, Does above two sql has same performance?

Business background, user could search goods by keyword or category or both, if user does not input keyword then keyword parameter default is empty string. I want to use the same sql, but worry performance. If keyword is empty should has a special query sql?

Answer 1

You could measure it using a profile; here the manual: http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

Start the profiler with

SET profiling = 1;

Then execute your Query. With

SHOW PROFILES;

you see a list of queries the profiler has statistics for. And finally you choose which query to examine with

SHOW PROFILE FOR QUERY 1;

or whatever number your query has.

What you get is a list where exactly how much time was spent during the query. Then you can decide which one was more perfomant