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 11 of 11
  1. #1
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Slow inefficent query (FIXED)

    Hello, rank stumbling newbie here

    I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously)

    My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows

    My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results

    The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results

    Anyhow
    Code:
    SELECT DISTINCT `Email` , `Title` , `FirstName` , `LastName`
    FROM All_Members, HB
    WHERE All_Members.Email != HB.EmailHB
    AND `ENewsletter` = 'y'
    AND `Email` != '' ''
    MYSQL Client 4.1.15
    PHPAdmin 2.9.1.1

    Thanks
    Last edited by Tynan; 03-20-2007 at 01:47 PM. Reason: problem solved

  • #2
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    There is nothing wrong with your query...

  • #3
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, that's good news I suppose

    Does it make sense then that the query, without the DISTINCT, returns a vast number of rows? like 10,000

    Does that suggest something not right in one of the tables?

    30s to query a table with 1200 rows and a table with perhaps a 100 rows is a warning sign isn't it?

  • #4
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Does it make sense then that the query, without the DISTINCT, returns a vast number of rows? like 10,000

    Does that suggest something not right in one of the tables?

    30s to query a table with 1200 rows and a table with perhaps a 100 rows is a warning sign isn't it?
    Really didn't get this...

  • #5
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry

    If I run that exact same query but without the DISTINCT in it, the query gives me over 10,000 results, ie loads of the rows repeated over and over again

    That suggest to me that the query isn't at all ideal, I assume in my ignorance that the DISTINCT is just patching up a bad query and the right query would return the right amount of rows without having to use DISTINCT

    So either the query isn't as good as it should be or I've done something silly in my dbs

    In all of the reasoning above, I'm guessing and assuming

  • #6
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    That's the power of DISTINCT keyword.

    If you want un-repeated results then you have to use DISTINCT in your query

  • #7
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    If your description of what you want is correct then your query is wrong, you're asking for those that are NOT in the second table - but the way in which you're doing it is causing the 10k rows - how many rows do you have in the HB table?

  • #8
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry Nancy, my slightly vague sentence I think, I'm filtering out any rows in All_Members that have an email address that exists in the HB tables (the query is defo right)

    There's 382 'bad' email addresses in the HB table
    There's 1,134 rows in the main All_Members table

    Some of the email addresses fields in the All_Members table are dups where people share a common email address, the HB Emails are unique

    I'm ecstatic about the way DISTINCT fixed the results, I just thought/think that there should be a query that brings back what I want cleanly without having to discard all the unwanted results using DISTINCT, so faster
    Last edited by Tynan; 03-20-2007 at 01:13 PM.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Your problem is here:
    Code:
    FROM All_Members, HB
    WHERE All_Members.Email != HB.EmailHB
    This is a cross join effect and will result in your query having to check every row for HB for each new row of All_Members.

    so if All_members has 1000 rows and HB has 1000 rows then your cross join will have an effect of looking through 1 million rows.

    You want this instead:
    Code:
    SELECT DISTINCT `Email` , `Title` , `FirstName` , `LastName`
    FROM All_Members
    WHERE 
    All_Members.Email NOT IN
       (SELECT  HB.EmailHB FROM HB)
    AND `ENewsletter` = 'y'
    AND `Email` != '' ''
    One other note, is that DISTINCT works on all columns in your select statement. So if you have two rows with the same Title, Firstname and Lastname but two different email addresses, both rows are returned.

    That is most likely what you want, but just pointing out that distinct does not work on just the one column.

  • #10
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    aaaah!

    I was dimly fumbling towards something like that and suspected soemthing horrid like that was happening

    Thank-you and apologies to all for my despicable level of knowledge, trying to crawl before I'm born
    Last edited by Tynan; 03-20-2007 at 01:22 PM.

  • #11
    Regular Coder
    Join Date
    Oct 2004
    Location
    London E4 UK
    Posts
    320
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's in and working like a dream, down from 30s to almost instant

    Got a few other suspect queries to rewrite now

    thank-you very much all of you

    not only does it work but I understand why too, I think I might have grasped the basics of the subquery too



  •  

    Posting Permissions

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