SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    small sql clause fix

    I have 3 MySQL tables called 'orders', 'orders_queue' and 'orders_sent'. The order number is primary key for all these tables, for orders table it's called "num", for orders_queue it's "ordernum" and for orders_sent it's "order_id".

    Clause: List max 10 order numbers sorted by Rand() that exist in table 'orders' but do not exist in 'orders_queue' nor in 'orders_sent' OR if it exists in 'orders_sent', these lines have also a field called 'foo' set to ''.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT orders.num
      FROM orders
     WHERE NOT EXISTS
           ( SELECT 'curly' 
               FROM orders_queue 
              WHERE ordernum = orders.num )
       AND NOT EXISTS
           ( SELECT 'larry' 
               FROM orders_sent 
              WHERE order_id = orders.num )
       AND NOT EXISTS
           ( SELECT 'moe' 
               FROM orders_sent 
              WHERE order_id = orders.num 
                AND foo <> '' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, but I will have hundreds of thousands of records in orders table. it will be slow SQL

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can there be any fast way to do this query ?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, you will still need to scan through the entire orders table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, you will still need to scan through the entire orders table
    what if Joins are used ?

    above SQL will go through all records. I need Max 10 records but also random

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    joins would be essentially the same

    have you tried my query? have you used an EXPLAIN on it?

    also, what is the purpose of showing only 10 random orders? why do they need to be random? what are you going to do with the results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually, my client is asking me for this kind of query
    he says , the above one is very slow ..

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    is there an index on ordernum? on order_id?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, these are just primary keys of their tables

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE for the orders_queue table please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you do a SHOW CREATE TABLE for the orders_queue table please
    sorry can't !

    my client has a big database and the above query wasn't completed
    so what if we just try the first 1000 records to be search from ?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you can't do a SHOW CREATE TABLE?

    i don't think i can help you further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hiddenpearls View Post
    sorry can't !
    My money says that you can't be helped further then.

  16. #16
    SitePoint Addict hiddenpearls's Avatar
    Join Date
    Dec 2007
    Location
    Pakistan
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you can't do a SHOW CREATE TABLE?

    i don't think i can help you further
    actually, i don't have it currently. I will get it and then will show u.
    never mind please!

  17. #17
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hiddenpearls View Post
    actually, i don't have it currently. I will get it and then will show u.
    That isn't what you suggested in your earlier post. If you can show the information you can be helped with it. Without the information we can't guess at what the difficulty is.


Bookmarks

Posting Permissions

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