473,743 Members | 2,247 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to optimize the join query...??

43 New Member
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

Expand|Select|Wrap|Line Numbers
  1. select table1.id,table2.time,table3.Status
  2. from table1 left join table2
  3. using(id)
  4. left join table3
  5. using (id)
  6. limit 10000
this gives result efficiently if limit is less but problem is with huge data set
when each table consist millions of records
any suggestion for optimizing it or any other way to do so
thanks in advance i am using mysql 4.1
Dec 22 '07 #1
11 1946
pbmods
5,821 Recognized Expert Expert
Heya, Bravo.

Your experience is quite common; joins are SLOW for large datasets.

You'd be better served in this case by either breaking it up into three separate queries, or by using subqueries.

You could try doing outer joins; sometimes that works:
Expand|Select|Wrap|Line Numbers
  1. SELECT table1.id,table2.time,table3.Status
  2. FROM table1, table2, table3
  3. WHERE table2.id = table1.id AND table 3.id = table 2.id
  4. LIMIT 10000
  5.  
Dec 23 '07 #2
bravo
43 New Member
Thanks
as per ur suggestions i tried subquerries but unfortunately still not getting any
fruitfull result.i cant break it into different queries due to some reasons...
i need the result set like
Table1
id | name
1 | a
2 | b
3 | c


Table2
id | time
2 | 0:0
3 | 0:1

Table 3
id | status
3 | true

I need the result set as
id | time | status
1 | null | null
2 | 0:0 | null
3 | 0:1 | true

but not getting it right through subqueries, getting right set using join but it is not efficient for huge set
plz help me out.... i am stuck
Dec 24 '07 #3
pbmods
5,821 Recognized Expert Expert
Heya, bravo.

What do you get if you run this query:
Expand|Select|Wrap|Line Numbers
  1. EXPLAIN SELECT table1.id,table2.TIME,table3.Status
  2. FROM table1 LEFT JOIN table2
  3. USING(id)
  4. LEFT JOIN table3
  5. USING (id)
  6. LIMIT 10000
  7.  
Dec 24 '07 #4
bravo
43 New Member
Heya, bravo.

What do you get if you run this query:
Expand|Select|Wrap|Line Numbers
  1. EXPLAIN SELECT table1.id,table2.TIME,table3.Status
  2. FROM table1 LEFT JOIN table2
  3. USING(id)
  4. LEFT JOIN table3
  5. USING (id)
  6. LIMIT 10000
  7.  
this is what i get when i run the above query....

id|select_type | table |type |possible_key| key|key_len|ref |rows|Extra
1 | simple | table1 | All | null | null | null | null | 149292 |
1 | simple | table2 | All | null | null | null | null | 100000 |
1 | simple | table3 | ref | id | id | 25 | host.table2.id | 10 |
Dec 25 '07 #5
pbmods
5,821 Recognized Expert Expert
Heya, Bravo.

this is what i get when i run the above query....

id|select_type | table |type |possible_key| key|key_len|ref |rows|Extra
1 | simple | table1 | All | null | null | null | null | 149292 |
1 | simple | table2 | All | null | null | null | null | 100000 |
1 | simple | table3 | ref | id | id | 25 | host.table2.id | 10 |
Ok. What we're going to do here is turn table1 into a derived table by using a subquery. This will instantly cut down the number of records that MySQL has to work with from 150,000 to the 10,000 that you need:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `dTable1`.`id`,
  3.         `table2`.`time`,
  4.         `table3`.`Status`
  5.     FROM
  6.     (
  7.         (
  8.             SELECT
  9.                     `id`
  10.                 FROM
  11.                     `table1`
  12.                 LIMIT 10000
  13.         ) AS `dTable1`
  14.         LEFT JOIN
  15.             `table2`
  16.                 USING(`id`)
  17.         LEFT JOIN
  18.             `table3`
  19.                 USING (`id`)
  20.     )
  21.     LIMIT 10000
  22.  
Dec 25 '07 #6
bravo
43 New Member
hi
thanks again
i am not an expert but have tried lot of techniques for optimizing this query but got the same result
even the above query gives result in same time as previous one
one more thing i noticed that if i implement paging then the time increases as a multiple like for first 100 rec 10 secs, next 100 20secs and so on.....
so joins are not going to help me
but using subqueries i am not getting null fields for the records not matching as mentioned previously...an d they are also slow same as joins

an optimized subquery for above query may be helpful but i am not getting it right

i am so confused not getting what to do now.... there must be some way to get out of such situations in mysql .....
Dec 26 '07 #7
pbmods
5,821 Recognized Expert Expert
Heya, Bravo.

Is each table indexed on the id column?
Dec 27 '07 #8
bravo
43 New Member
Heya, Bravo.

Is each table indexed on the id column?

yes every table is indexed on the id column
as id is unique and is same in each table
does this effecting the performance???? ?


also i have one doubt over indexing ....
if in a table updation, insertion,delet ion is done
frequently then does indexing effects the performance
ie will it have some negative impact..

and a humble request for u to reply soon as i dont think anybody
else in this forum is interested in this....
Dec 27 '07 #9
pbmods
5,821 Recognized Expert Expert
Heya, Bravo.

Generally, indexing is a good thing. Especially for large recordsets, the benefits to having an indexed table far outweigh the overhead associated with maintaining those indexes.

At this point, I think your best bet would be to create an index table. Create a table that contains the results of your query and then run a periodic script to update it.

For maximum efficiency, you might want to go in and create some MySQL triggers to keep your index table auto-updated.
Dec 27 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
2096
by: mjuricek | last post by:
I'm having some problems to optimize my stored procedure (select statement with joins) What I'm trying to do is calculate total work. My situation: I have 3 tables I'm using -Input (char destA, char destB, int work)where I have all input data (moves from destA to destB) around 3000 rows
1
1392
by: malcolm | last post by:
This one's kind of hard to explain, so I've opted to post a simplified version of our view that prompted me to ask this question: The question is re-asked after the view... create view MainView ( PrimaryKeyID, SubTotal1, SubTotal2, GrandTotal )
0
1826
by: Michal Hlavac | last post by:
Hello, I have one main table and 2 many-to-many relation tables and 2 one-to-many relation table. Main table has 150 000 rows and many-to-many tables have about 300 000 rows. I am creating web portal. In my query I must call distinct with many-to-many relation. But this query is too slow for web application. This query costs 8,5 seconds. For example:
4
2031
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: ------------------------------------------------------------------------------------- table type possible_key key key_len ref rows Extra t1 const PK, name PK 4 const 10 t3 const PK PK 4 const 10
19
1668
by: octangle | last post by:
This code is attempting to find records that have a RegJrnID that does not occur more than one time in the table. The reason that I want to find records with non-duplicated RegJrnID values is to create "reversal" records for these such that the reversal record has identical values for every column except the TaxableAmount which will contain a negative amount. (see: example data below). /* Set up */
1
2443
by: rajeshkapadi | last post by:
Please help me optimize this: I have a table with columns: headlineid, keyword. headlineid+keyword combination is unique. Relationship between headline and keyword is many-to-many. i.e., headlines can have many keywords. keywords can be associated with many headlines. Keywords in the same headline are considered "related".
1
1625
by: kmugunda | last post by:
Hi below is a query where certain set of data is fetched from a table based on certain condition, but the execution takes lot of time , is there a way we can optimize the code below SELECT DISTINCT a.CNOTE_BOOK_ID, a.CNOTE_SERIES, a.AGENT_CODE, a.OPERATOR FROM db2inst1.CNGC_BOOK a LEFT OUTER JOIN db2inst1.CNGC_NOTE b ON a.CNOTE_BOOK_ID = b.CNOTE_BOOK_ID LEFT OUTER JOIN db2inst1.CMCN_SERIES c ON a.CNOTE_SERIES = c.CODE LEFT...
2
1971
by: bravo | last post by:
hi everyone , i am trying to join two tables , the query is working fine when the records in the tables are less but when the records are in huge numbers say 1-2 million then the system hangs... i am using version 4.1 and the query is SELECT COUNT(dummy.username)AS count FROM dummy2 LEFT JOIN dummy ON dummy2.msg_id = dummy.msg_id where dummy2.username like 'xyz' GROUP BY dummy2.username plz give any suggestions to optimize this so...
12
3395
by: Yash | last post by:
My SQL query is like: SELECT ..... FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more joins) WHERE C.created_date between @start_date and @end_date AND C.claimant_id=@claimant_id or @claimant_id=-1 AND (more conditions like the claimant condition) This query is in a stored proc which takes parameters @start_date,
0
8970
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9344
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9277
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8218
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6763
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6057
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4572
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3286
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2759
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.