Golang slow performance inside for loop MySQL queries

Getting Help Code Review
1

I use Golang with this driver -> “github.com/go-sql-driver/mysql” in order to connect to MYSQL. The performance in GO is terrible or I’m missing something.

We have a table with posts. We fetch the posts, on each post we search favorites table (if user has favorited this post and if he likes it)

posts := make([]*MembersModel.Post, 0, 6)

postsResults, err := Config.DB.Query(“SELECT id, b64, title, description, total_likes, total_favorites, published_date FROM posts ORDER BY id DESC LIMIT 6”)

defer postsResults.Close()

if err != nil {
    fmt.Println(err)
    panic(err.Error()) 
}

for postsResults.Next() {
    var postID int
    var b64 string
    var title string
    var description string
    var total_likes int
    var total_favorites int
    var published_date string

    postsResults.Scan(&id, &b64, &title, &description, &total_likes, &total_favorites, &published_date)

    var count int
    var favorited string

    fetchBookmarks := Config.DB.QueryRow("SELECT COUNT(*) FROM favorites where userID = ? and postID = ? and status = ?", userID, postID, "added").Scan(&count)

    if fetchBookmarks != nil {
        fmt.Println("error")
    }

    if count == 0 {
        favorited = "no"
    } else {
        favorited = "yes"
    }

    var countSubmitted int
    var likedPost string

    fetchLikes := Config.DB.QueryRow("SELECT COUNT(*) FROM likes where userID = ? and postID = ? and status=?", userID, postID, "liked").Scan(&countSubmitted)

    if fetchLikes != nil {
        fmt.Println("error")
    }

    if countSubmitted == 0 {
        likedPost = "no"
    } else {
        likedPost = "yes"
    }


    post := &MembersModel.JobList{
        PostID:        b64,
        Title: title,
        Description:     description,
        Likes:   total_likes,
        PubDate:   published_date,
        Bookmarked:   favorited,
        Liked:     likedPost,
    }

    posts = append(posts, post)

}

Average time to fetch these results -> 10 seconds!

If I exclude the MYSQL calls inside the loop, the time to fetch these results is 300ms .

(Likes-Favorites tables have 10 rows only)

2

Use a single prepared statement for each kind of subquery instead of ad-hoc ones.

Also you checkout a new connection for each from the pool, that’s another cost factor, try to reuse a single connection.

3

Thanks for your response. Can you give me an example with the above code? How would you create them with prepared statements? And how can I reuse a single connection?

4

Have you received the answer for your question.

5

What is wrong in this code .