UPDATE: Since the attributes i was filtering where divided into groups, i broke down the huge table into smaller tables, each of which contains full information for a specific group. So instead of one table with 13 million rows, i now have many tables each of which has about 800,000 - 900,000 rows.
Rewrote the query like so: SELECT a.num FROM a WHERE EXISTS (SELECT 'x' FROM m WHERE a.id = m.a_id AND (m.attr_id = x OR m.attr_id = y ...)) AND EXISTS (... FROM n ...) This proved to be the fastest, now the query is taking somewhere between 10 seconds and 3 minutes, depending on how many conditions i throw at it. After all this, i finally understand why DBAs get a 6 figure salary !! On 6/18/07, Al-Faisal El-Dajani <[EMAIL PROTECTED]> wrote: > > Jad: Thanx for the idea, i'm actually working on something similar, hope > to provide you with an update by this weekend. Since the database is read > only (no inserts or updates will ever occur), i'm thinking of fragmenting > table B into smaller tables, and continuing from there. > > Xushi and Zaid: Thanx, but unfortunately, i can't modify the server > configuration, and upgrading hardware is probably not an option. The current > state of the query is somewhat acceptable, since it'll be run like at most > 2-3 times a day.. so it's ok if they wait a minute. I'm just trying to > enhance it more just for the sake of breaking the boundary. > > Aram: Thanx, now i know why it didn't work like expected. MySQL threw me > quite a few surprises during this project, glad to understand one of them!! > > On 6/18/07, Aram Yegenian <[EMAIL PROTECTED]> wrote: > > > > > > > > --- Al-Faisal El-Dajani <[EMAIL PROTECTED]> > > wrote: > > > > > I was extremely > > > surprised to see the first > > > query (join) was actually FASTER than my second > > > query (nested). The only > > > thing i managed to conclude was that MySQL doesn't > > > support indices in inner > > > statements (not sure, if you have a better > > > explanation, please do share). > > > > mysql doesn't optimize subqueries, or as is it says in > > the docs "The optimizer is more mature for joins than > > for subqueries" > > http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html > > > > - Aram > > > > > > > > > > > > > > ____________________________________________________________________________________ > > Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's > > updated for today's economy) at Yahoo! Games. > > http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow > > > > > > > > > > > -- > Al-Faisal El-Dajani > Phone: +962-7-77 799 781 > P.O Box: 140056 > 11814 Amman, Jordan > -- Al-Faisal El-Dajani Phone: +962-7-77 799 781 P.O Box: 140056 11814 Amman, Jordan --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Jolug" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Jolug?hl=en-GB -~----------~----~----~----~------~----~------~--~---
