• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How can I rate query performance?

#1
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 1

When 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)?
Reply

#2
@skunkbad,

Here you go... https://stackoverflow.com/questions/1127...query-time
Reply

#3
(09-07-2018, 09:53 AM)php_rocs Wrote: @skunkbad,

Here you go... https://stackoverflow.com/questions/1127...query-time

Looks perfect. Thanks!
Reply

#4
This is more of a question, but could you use the benchmarking class??

https://www.codeigniter.com/userguide3/l...hmark.html
Practical guide to IgnitedCMS - Book coming soon
Reply

#5
(09-07-2018, 10:35 AM)ignitedcms Wrote: This is more of a question, but could you use the benchmarking class??

https://www.codeigniter.com/userguide3/l...hmark.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.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.