CODEWITHSUNDEEP
mysql
Adrian
Adrian

Reputation: 15

Mysql - What is the fastest way to retrieve records from multiple tables

example: i create table post -

post_id primary key

content varchar

and then i create table comment -

comment_id primary key

post_id foreign key reference post(post_id)

content varchar

show records in ASP(Active Server Pages)

query1: select * from post;

Do while NOT RS1.EOF
{
response.write RS1("post_id")
response.write RS1("content")

query2: select * from comment where post_id = RS1("post_id")

Do while NOT RS2.EOF
{
    response.write RS2("comment_id")
    response.write RS2("content")
}
}

the second query perform a fullscan in table comment for each record in table post, is there any way to make comment search faster than the above method? do i need to use index for that case? Thanks in advance

Upvotes: 0

Views: 165

Answers (2)

ethrbunny
ethrbunny

Reputation: 10469

Add an index for 'post_id' to 'comment'.

You could also do a join between these two tables to get all the comments for each post using one query. This might be expensive if you have lots of posts though...

Upvotes: 2

John Woo
John Woo

Reputation: 263733

JOIN the tables,

SELECT  a.*, b.*
FROM    post a
        INNER JOIN comment b
            ON a.Post_ID = b.Post_ID
-- WHERE   a.Post_ID = @valueHere       -- <<== if you want to get specific post

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 1

Related Questions