|
|
Posted by Gert-Jan Strik on 11/18/06 14:02
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:
>
> 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?
Navigation:
[Reply to this message]
|