how to optimize the join query...??

[bravo]

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 table1 left join table2
using(id)
left join table3
using (id)
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

[pbmods]

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:

SELECT table1.id,table2.time,table3.Status
FROM table1, table2, table3
WHERE table2.id = table1.id AND table 3.id = table 2.id
LIMIT 10000
 

[bravo]

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

[pbmods]

Heya, bravo.

What do you get if you run this query:

EXPLAIN SELECT table1.id,table2.TIME,table3.Status
FROM table1 LEFT JOIN table2
USING(id)
LEFT JOIN table3
USING (id)
LIMIT 10000
 

[bravo]

Heya, bravo.

What do you get if you run this query:

EXPLAIN SELECT table1.id,table2.TIME,table3.Status
FROM table1 LEFT JOIN table2
USING(id)
LEFT JOIN table3
USING (id)
LIMIT 10000
 

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 |

[pbmods]

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:

SELECT
        `dTable1`.`id`,
        `table2`.`time`,
        `table3`.`Status`
    FROM
    (
        (
            SELECT
                    `id`
                FROM
                    `table1`
                LIMIT 10000
        ) AS `dTable1`
        LEFT JOIN
            `table2`
                USING(`id`)
        LEFT JOIN
            `table3`
                USING (`id`)
    )
    LIMIT 10000
 

[bravo]

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...and 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 .....

[pbmods]

Heya, Bravo.

Is each table indexed on the id column?

[bravo]

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,deletion 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....

[pbmods]

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.

[bravo]

Thanks a lot for ur help....
i will try it .....
and update if i will get success some how.... may be some one else
get benifit....

[pbmods]

Heya, Bravo.

Good luck, and if it gives you any trouble, post back anytime :)