Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts

    Daft question.. feel stupid asking it ;)

    Ok, I'm running this query:
    Code:
    select *, (select count(*) from mail_emails as emails where user_id = '110' and `read` = '0' and emails.folder_id = folders.folder_id) as unread, (select count(*) from mail_emails as emails where user_id = '110' and `read` = '1' and folder_id = folders.folder_id) as `read`, (select count(*) from mail_emails as emails where user_id = '110' and folder_id = folders.folder_id) as `total` from mail_folders as folders join mail_folder_names as names on folders.folder_name_id = names.folder_name_id join mail_folder_types as types on types.type_id = folders.type_id where folders.user_id = '110' order by sys_type asc, folder asc, types.type_id asc

    Now obviously in my mysql log, that all shows up. What I'd like to know is, does that all count as seperate queries or just one large query?
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #2
    Regular Coder LearningCoder's Avatar
    Join Date
    Jan 2011
    Location
    The Pleiades
    Posts
    912
    Thanks
    76
    Thanked 28 Times in 28 Posts
    Hi dude,

    Not sure if you have come across this but I bookmarked this the other night because I've used one in my current site.

    http://dev.mysql.com/tech-resources/...es_part_1.html

    Probably more of a Fou question.

    Regards,

    LC.

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Not a Fou question, that would be an old pedant question
    I would expect that each one needs to evaluate. I think that dbms' in general can do some cache work on constant data or non-variable subqueries, but these all have a reliance on the record itself on the mail_emails.folder_id = mail_folders.folder_id.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    Hmm so in other words I need to somehow clean that up too... humbug!
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    First of all, let's make that *READABLE*:
    Code:
    select *, 
           (select count(*) 
             from mail_emails as emails 
             where user_id = '110' and `read` = '0' 
             and emails.folder_id = folders.folder_id) as unread, 
           (select count(*) 
             from mail_emails as emails 
             where user_id = '110' and `read` = '1' 
             and folder_id = folders.folder_id) as `read`, 
           (select count(*) from mail_emails
            as emails where user_id = '110' 
            and folder_id = folders.folder_id) as `total` 
    from mail_folders as folders join mail_folder_names as names 
    on folders.folder_name_id = names.folder_name_id 
    join mail_folder_types as types on types.type_id = folders.type_id 
    where folders.user_id = '110' 
    order by sys_type asc, folder asc, types.type_id asc
    Okay, you have, essentially, 4 separate queries there.

    You also are doing a really really bad thing by starting with SELECT *.

    *SURELY* you don't need (just to pick one example) to get both types.type_id and folders.type_id when they are identical. (In fact, getting both can cause problems in your PHP code.) And of course there is no reason at all to get user_id when you have explicitly limited it to just user_id='110' (which, by the by, is another error: you should not put numbers in apostrophes if the field you are comparing to is a numeric field--it works because MySQL sloppily allows it, but it's a very bad habit to get into).

    ANYWAY...you could easily chop that down to TWO queries, thus:
    Code:
    SELECT explicit, list, of, needed, fields, only, X.total, X.read, X.unread
    FROM ( SELECT COUNT(*) AS total, 
                  SUM(read) AS read,
                  -SUM(1-read) AS unread
           FROM mail_emails WHERE user_id = 110 ) AS X,
         mail_folders AS folders, 
         mail_folder_names AS names,
         mail_folder_types AS types
    WHERE folders.folder_name_id = names.folder_name_id 
      AND types.type_id = folders.type_id 
      AND folders.user_id = 110
    ORDER BY sys_type ASC, folder ASC, types.type_id ASC
    Something like that, at least.

    That code depends on the value of mail_emails.read being *ONLY* 1 or 0, of course.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    tangoforce (03-11-2013)

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,091
    Thanks
    51
    Thanked 506 Times in 493 Posts
    old pedant, you're a legend I'll give that a whirl and see what I can do with it
    My helpful sig is on vacation trying to loose some weight. It got a bit fat and caused a few problems but it will be back at some point!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •