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 ‘’.

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 <> '' ) 

:cool:

thanks, but I will have hundreds of thousands of records in orders table. it will be slow SQL

you’re welcome :slight_smile:

can there be any fast way to do this query ?

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 :slight_smile:

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?

actually, my client is asking me for this kind of query :slight_smile:
he says , the above one is very slow …

is there an index on ordernum? on order_id?

no, these are just primary keys of their tables

could you do a SHOW CREATE TABLE for the orders_queue table please

sorry can’t !:confused:

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

you can’t do a SHOW CREATE TABLE?

i don’t think i can help you further

My money says that you can’t be helped further then. :rolleyes:

actually, i don’t have it currently. I will get it and then will show u.
never mind please!

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.