Optimize query

Hello,

I am trying to find all records that have matching detail_1 or detail_2
columns. This is what I have now:

select t1.id, t1.detail_1, t1.detail_2, t2.id from user_details t1,
user_details t2
where t1.detail_1 = t2.detail_1 or t1.detail_2 = t2.detail_2;

Using smaller tables of around 1000 records, I have verified that this
indeed does the job. However, when I apply it to the real table that
has around 40,000 records, it takes so long that I have to stop the
query.

The table in question is quite simple and is created as follows. There
are no indexes on the table:

create table user_details (id integer, detail_1 varchar(50), detail_2
varchar(50)

Is there a way to make it go faster?newtophp2000@.yahoo.com wrote:

Quote:

Originally Posted by

The table in question is quite simple and is created as follows.
There are no indexes on the table:
>
create table user_details (id integer, detail_1 varchar(50),
detail_2 varchar(50)
>
>
Is there a way to make it go faster?

Well I'm no expert, but surely the way to make it go faster is to index
detail_1 and detail_2?

For every record selected in t1, the engine is comparing 40,000 records
in t2. 40,000 x 40,000 is 1,600,000,000 operations.

Of course, I might be talking nonsense...
--
SlowerThanYou|||Start by giving the tables the proper keys (you know PRIMARY KEY,
FOREIGN KEY, etc.)

Then index the columns that are used in the join predicates.

That should give you proper performance. If you still don't like the
performance after that, you could rewrite the query to the syntax below,
and see if that improves performance:

SELECT t1.id, t1.detail_1, t1.detail_2, t2.id
FROM user_details t1
INNER JOIN user_details t2
ON t1.detail_1 = t2.detail_1

UNION

SELECT t1.id, t1.detail_1, t1.detail_2, t2.id
FROM user_details t1
INNER JOIN user_details t2
ON t1.detail_2 = t2.detail_2

If you are only want to find out if there are any matches, and don't
care how many matches there are, you could run the query below for each
column you want to probe:

SELECT detail_1, MIN(id), MAX(id)
FROM user_details
GROUP BY detail_1
HAVING COUNT(*)>1

HTH,
Gert-Jan

newtophp2000@.yahoo.com wrote:

Quote:

Originally Posted by

>
Hello,
>
I am trying to find all records that have matching detail_1 or detail_2
columns. This is what I have now:
>
select t1.id, t1.detail_1, t1.detail_2, t2.id from user_details t1,
user_details t2
where t1.detail_1 = t2.detail_1 or t1.detail_2 = t2.detail_2;
>
Using smaller tables of around 1000 records, I have verified that this
indeed does the job. However, when I apply it to the real table that
has around 40,000 records, it takes so long that I have to stop the
query.
>
The table in question is quite simple and is created as follows. There
are no indexes on the table:
>
create table user_details (id integer, detail_1 varchar(50), detail_2
varchar(50)
>
Is there a way to make it go faster?

|||Slower Than You wrote:

Quote:

Originally Posted by

Well I'm no expert, but surely the way to make it go faster is to index
detail_1 and detail_2?
>
For every record selected in t1, the engine is comparing 40,000 records
in t2. 40,000 x 40,000 is 1,600,000,000 operations.

Yes, indeed the indexes helped the query. Thanks a lot!

Quote:

Originally Posted by

Of course, I might be talking nonsense...
--
SlowerThanYou

|||deebeetwo@.yahoo.com wrote:

Quote:

Originally Posted by

Slower Than You wrote:

Quote:

Originally Posted by

Well I'm no expert, but surely the way to make it go faster is to
index detail_1 and detail_2?

For every record selected in t1, the engine is comparing 40,000
records in t2. 40,000 x 40,000 is 1,600,000,000 operations.

>
>
Yes, indeed the indexes helped the query. Thanks a lot!

Wow! You mean I was actually *right* about something? Well that makes a
pleasant change...

Glad to be of some assistance.
--
SlowerThanYousql