+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: General (https://forum.codeigniter.com/forumdisplay.php?fid=1)
+--- Forum: Lounge (https://forum.codeigniter.com/forumdisplay.php?fid=3)
+--- Thread: How can I rate query performance? (/showthread.php?tid=71631)
How can I rate query performance? - skunkbad - 09-07-2018
Sometimes we can write a query multiple ways, or break down a query into multiple queries, so I wonder how to rate the performance of the query (or queries) to know what will have the best performance.
I'm thinking about this this morning because I have a query like this:
Code:
SELECT
u.username,
GROUP_CONCAT( DISTINCT r.role SEPARATOR "," ) roles,
GROUP_CONCAT( DISTINCT g.group SEPARATOR "," ) groups
FROM users u
LEFT JOIN user_roles r
ON u.user_id = r.user_id
LEFT JOIN user_groups g
ON u.user_id = g.user_id
WHERE(
LOWER(u.username) = ? OR LOWER(u.email) = ?
)
GROUP BY u.user_id
LIMIT 1When I use EXPLAIN it looks like it's doing 3 queries, and I could just do 3 separate queries, but how will I know which way is best/fastest? Is there a way to rate the query (some numeric value)?
RE: How can I rate query performance? - php_rocs - 09-07-2018
@skunkbad,
Here you go... https://stackoverflow.com/questions/11274892/measuring-actual-mysql-query-time
RE: How can I rate query performance? - skunkbad - 09-07-2018
(09-07-2018, 08:53 AM)php_rocs Wrote: @skunkbad,
Here you go... https://stackoverflow.com/questions/11274892/measuring-actual-mysql-query-time
Looks perfect. Thanks!
RE: How can I rate query performance? - ignitedcms - 09-07-2018
This is more of a question, but could you use the benchmarking class??
https://www.codeigniter.com/userguide3/libraries/benchmark.html
RE: How can I rate query performance? - skunkbad - 09-07-2018
(09-07-2018, 09:35 AM)ignitedcms Wrote: This is more of a question, but could you use the benchmarking class??
https://www.codeigniter.com/userguide3/libraries/benchmark.html
I believe the benchmarking class is taking total execution time into account, which is mostly PHP, and isn't specifically what I'm interested in. Ideally I can run my queries via the terminal, so PHP won't even be a factor.