469,133 Members | 1,065 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

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?

Nov 17 '06 #1
4 1670
ne**********@yahoo.com wrote:
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
Nov 17 '06 #2
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
ne**********@yahoo.com wrote:
>
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?
Nov 18 '06 #3
Slower Than You wrote:
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!
Of course, I might be talking nonsense...
--
SlowerThanYou
Nov 20 '06 #4
de*******@yahoo.com wrote:
Slower Than You wrote:
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.
--
SlowerThanYou
Nov 21 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Andreas Falck | last post: by
6 posts views Thread by Bruce D | last post: by
5 posts views Thread by xeqister | last post: by
3 posts views Thread by Reddy | last post: by
4 posts views Thread by Huaer.XC | last post: by
13 posts views Thread by Frank Swarbrick | last post: by
1 post views Thread by acornejo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.