Re: Can anyone figure out this MySQL query?

In article <39acf72e.85704937_at_news.arnes.si>, do-not_at_spam.me wrote:
>Hi Theo;
>
>Thanks! I've tried the first two and it's working OK with the DISTINCT
>option, because I don't need duplicates. However the query is rather
>slow on my dataset which is almost 1 million Invoices - it takes about
>12 minutes to complete the query. Do you think that indexes may
>speedup the query to, egad...a few seconds?
Hmm.
Mysql first has to scan one million records to find customers 1 and 2 in the "a" table, then start over for customers 1 and 2 in the "b" table, then compare CustomerNo and HotelNo. I suppose both comparisons will be quick since they only involve a relatively small number of records. Both searches should speed up significantly if you specify an index on CustomerNo.

The wrong way for Mysql to operate would be to compare HotelNumbers first (one million times one million comparisons), then to compare CustomerNumbers among matching "a" and "b" hotels resulting in an explosion of all pairs of different customers that ever stayed in "a" and "b" hotels, and finally to leave out any pairs with other customers than 1 and 2.
>
>I will try the last solution you presented in the meantime and see how
>that pans out.
>
>Thanks again,
> Chris
>
>On Wed, 30 Aug 2000 09:52:48 GMT, peterbroers_at_rhbcml.leidenuniv.nl
>(Theo Peterbroers) wrote:
>
>>Simple, if you are only interested in a case with two customers:
>>SELECT a.*
>>FROM Invoices as a, Invoices as b
>>WHERE a.CustomerNo in (1,2)
>> AND b.CustomerNo in (1,2)
>> AND a.CustomerNo <> b.CustomerNo
>> AND a.HotelNo = b.HotelNo
>>The trick is to use two aliases (a and b) for the same table.
>
Received on Wed Aug 30 2000 - 16:43:25 CEST