hiddenpearls — 2010-02-18T08:45:48-05:00 — #1
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 ''.
r937 — 2010-02-18T08:56:44-05:00 — #2
WHERE NOT EXISTS
( SELECT 'curly'
WHERE ordernum = orders.num )
AND NOT EXISTS
( SELECT 'larry'
WHERE order_id = orders.num )
AND NOT EXISTS
( SELECT 'moe'
WHERE order_id = orders.num
AND foo <> '' )
hiddenpearls — 2010-02-18T09:15:20-05:00 — #3
thanks, but I will have hundreds of thousands of records in orders table. it will be slow SQL
r937 — 2010-02-18T09:18:57-05:00 — #4
hiddenpearls — 2010-02-18T09:19:58-05:00 — #5
can there be any fast way to do this query ?
r937 — 2010-02-18T09:40:44-05:00 — #6
no, you will still need to scan through the entire orders table
hiddenpearls — 2010-02-18T09:58:26-05:00 — #7
what if Joins are used ?
above SQL will go through all records. I need Max 10 records but also random
r937 — 2010-02-18T10:01:49-05:00 — #8
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?
hiddenpearls — 2010-02-18T10:15:55-05:00 — #9
actually, my client is asking me for this kind of query
he says , the above one is very slow ..
r937 — 2010-02-18T10:18:06-05:00 — #10
is there an index on ordernum? on order_id?
hiddenpearls — 2010-02-18T10:21:01-05:00 — #11
no, these are just primary keys of their tables
r937 — 2010-02-18T10:34:20-05:00 — #12
could you do a SHOW CREATE TABLE for the orders_queue table please
hiddenpearls — 2010-02-18T10:45:03-05:00 — #13
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 ?
r937 — 2010-02-18T10:47:35-05:00 — #14
you can't do a SHOW CREATE TABLE?
i don't think i can help you further
guelphdad — 2010-02-18T10:48:34-05:00 — #15
My money says that you can't be helped further then. :rolleyes:
hiddenpearls — 2010-02-18T10:54:00-05:00 — #16
actually, i don't have it currently. I will get it and then will show u.
never mind please!
guelphdad — 2010-02-18T15:44:10-05:00 — #17
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.