473,241 Members | 1,658 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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 1768
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Andreas Falck | last post by:
Hi, I ran the code below on two different versions, 4.0.14 and 4.0.16 respectively, both running RH 7.3 on intel. In version mysql server version 4.0.14 the SELECT privelege suffices for...
6
by: Bruce D | last post by:
Could someone please help to explain why the following query isn't using the index... explain select id from kbm where state = 'MA' table type possible_keys key key_len ref rows Extra...
3
by: Paul Janssen | last post by:
Hello! Can anyone help me out with the following situation: (a) a single query with 550 id's in the IN-clause resulting into 800+ seconds; (b) 550 queries with a single id in the IN-clause...
5
by: xeqister | last post by:
Greetings all, We have a complicated statement in DB2 which takes long hour to complete and we have created most of the indexes. Does anybody knows how to tune the following statement to optimize...
3
by: Reddy | last post by:
The sql query for my datagrid returns 100, 000 records. But the datagrid should display 20 records per page. I am using datagrid paging, but it is taking too much time for the page to load. Is...
4
by: Huaer.XC | last post by:
>From the following MySQL command: EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON t3.name = t1.name WHERE t1.id IN(123, 124); which result is:...
13
by: Frank Swarbrick | last post by:
IBM has a product for the VSE operating system called the VSAM Redirector. It allows you to use VSAM to access RDBMS tables/views as if they were actual VSAM files. We're doing a comparison right...
11
by: bravo | last post by:
hi i need to optimize the join query, which joins three tables say table1 ,table2 , table3 each having huge volume of records... the query is as select table1.id,table2.time,table3.Status from...
1
by: acornejo | last post by:
Hi All I've the following code I need to optimize. Currently tblOutgoing is about 250K registers and growing at a rate of about 20k records per day. This code takes me over 5 secs to run on each...
3
zabsmarty
by: zabsmarty | last post by:
Can any one help me to make my query code optimize and load faster. Please help me or any example what steps should we use to optimize. Thank You
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.