Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Which is More Optimal: Using GORM's Pluck or Raw Query with foreach to Store IDs in a Slice?

I’m working on a Go project using GORM and MySQL. I need to retrieve a list of IDs from a specific entity in the database and am considering two approaches to achieve this. I’d like to know which one is more optimal in terms of performance:

  1. Using Pluck in GORM: I can use the Pluck method provided by GORM to directly extract IDs from the query results.

Example code:

var ids []int
db.Model(&YourModel{}).Pluck("id", &ids)
  1. Using Raw Query and foreach to Store IDs in a Slice: Alternatively, I can use a raw query and then manually store the retrieved IDs into a slice.

Example code:

rows, err := db.Raw("SELECT id FROM your_table").Rows()
if err != nil {
    // handle error
}
defer rows.Close()

var ids []int
for rows.Next() {
    var id int
    if err := rows.Scan(&id); err != nil {
        // handle error
    }
    ids = append(ids, id)
}

Question:

From a performance and efficiency standpoint, which method is better to use in this case, GORM’s Pluck or raw query with foreach? Are there other factors I should consider when choosing between these methods? I appreciate any advice or experiences from experts.

Thank you!

Tags:

2

Answers


  1. 0

    If your need is only to get all the ids of a given model, I would choose the Pluck solution to avoid memory reallocation induced by the ids = append(ids, id) statement.

    Login or Signup to reply.
  2. 0

    Getting a bunch of ids and then issuing queries using them is much less efficient (in the database) than doing all the work inside the database.

    That is, JOIN to the other table before returning to the client.

    I am not familiar with GORM, so I can’t elaborate, but here is a crude fabricated example:

    SELECT b.this, b.that
    FROM a
    JOIN b ON a.b_id = b.id
    WHERE a.whatever > 123
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search