mem bob <[EMAIL PROTECTED]> wrote on 10/24/2005 09:45:30 AM: > Hi all, > > Can anyone throw some suggestions at me for this problem? > > | id | model | service_id | > |----|-------|------------| > | 1 | 500 | 1 | > | 2 | 500 | 3 | > | 3 | 500 | 10 | > | 4 | 600 | 1 | > > > From this table i want to extract all distinct models which have > service_id=1 *AND* service_id=3 > > What's the best possible (speed) solution for this - I have a lot of records > to juggle about! > > I have tried inner joining it within it's self but this method could get > rather complicated. > I have also tried making a count of service_id's captured and HAVING count > == 2 but this query seems slow. > > Any suggestions or pointers would be great! > > Thanks > > Membob
You have already mentioned the two most popular techniques. The self-join is more complicated to write and does not scale well to more than 2 or 3 search terms but it can be quite fast (depending on your index coverage). The COUNT() ... HAVING techinique is more flexible (scales better, too) but can take more time to compute (also based on your index coverage). How may different sets of terms do you need to test? If you are going to be running a lot of different lists of service_ids through a query, you probably ought to stick with the COUNT()...HAVING techniqe but this time add an index of (model, service_id) so that you no longer need to query the actual table. ALTER TABLE yourtablename ADD KEY(model, service_id); You can drop the index after your ad-hoc analysis (or not, choice is always yours) however it will make both techniques faster. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
