What's the difference between "rows scan" and "page limit" in mysql?

user_17745619 0
Viewed 44

I have a SELECT sentence in mysql:

SELECT * FROM `my_flows` WHERE deleted = 0 AND status = 0 AND end_time > '2022-02-03 13:54:48.173'

and there are indexes in columns deleted态status and end_time and a primary key id.

I wan't to get all the results for processing and I have two ways:

  1. use rows scan like this in gorm:
rows, err := db.Model(&ModelInfo{}).Where(...).Rows()
// ignore error judge
defer rows.Close()
for rows.Next() {
    ....
}
  1. use page limit which I record the latest id for next page:
var latestId int64
for {
    var result []*ModelInfo
    err := db.Where(...).Where("id > ?", latestId).Order("id").Limit(10).Find(&result).Error()
    // ignore error judge
    if len(result) == 0 {
        break
    }
    for _, r := range result {
        .......
        latestId = result.Id
    }
}

I want to know which is the best? Please help me, thanks very much.

go-gorm go mysql
1 Answers

You should use offset method. also order by id.

var page int
for {
    var result []*ModelInfo
    db.Model(&model{}).Where(...).Offset(10 * page).Limit(10).Order("id").Find(&result).Error()
    if len(result) == 0 {
        break
    }
    page++
    for _, r := range result {
        .......
        latestId = result.Id
    }
}
user_19578350
Related