SQL very slow while query from a huge database table

[rts-gordon]

Your Question

Hi there,

There is a huge MySQL table in my system, more than 14,000,000 records in it, and there are primary key and indexes.

Query 200 records from DB via DBeaver with raw SQL, it takes 110ms; Query 200 records from DB via GORM , it takes 22s, is there something wrong.

There are GORM config: MySQL: version 8 Golang: 1.22.1 GORM: v1.25.10 driver/mysql v1.5.7

Can you please take a look at this, thanks a lot for this.

Query from a huge table will take less than 500ms via GORM.

[ivila]

@rts-gordon You can just open profile in your mysql server and check what's going on. From my experience, it might due to the stupid behavior of SQL optimizer when using parameterized sql (gorm use it automatically to prevent SQL injection, which I think you didn't try it when you test via DBeaver with raw SQL).

You can use force index to make it use the right index you want, just don't rely on the SQL optimizer of MySQL, it's stupid in some scenarios(especially when you have lots of records in your table)

[rts-gordon]

Hi @ivila Thanks a lot for your answer. I did some optimize for query, add force index to SQL:

Raw SQL in GORM:

SELECT login, username FROM my_table FORCE INDEX (login) WHERE login = 123456

Use GORM

err := db.DB().Clauses(hints.ForceIndex("IDX_login")).Table("my_table").Select("login, username").Where("login = ?", 123456).Find(&list1).Error

There are errors in BOTH SQL,

Error 1176 (42000): Key 'IDX_login' doesn't exist in table 'my_table'

There is an index "IDX_login" on column "login" and it is visiable.

Is there miss index in GORM? or any thing I did wrong?

Thank you again.

[ivila]

Hi @ivila Thanks a lot for your answer. I did some optimize for query, add force index to SQL:

Raw SQL in GORM:

SELECT login, username FROM my_table FORCE INDEX (login) WHERE login = 123456

Use GORM

err := db.DB().Clauses(hints.ForceIndex("IDX_login")).Table("my_table").Select("login, username").Where("login = ?", 123456).Find(&list1).Error

There are errors in BOTH SQL,

Error 1176 (42000): Key 'IDX_login' doesn't exist in table 'my_table'

There is an index "IDX_login" on column "login" and it is visiable.

Is there miss index in GORM? or any thing I did wrong?

Thank you again.

Maybe you can submit a minimum reproduction example first, I test your codes myself, it runs without any error. Here is the SQL I use to create the table:

CREATE TABLE my_table (
  id bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  login bigint(11) unsigned  NOT NULL,
  username varchar(256) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_login`(`login`)
);

here is the code I run for testing

func test_run(dsn string) {
        db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
        if err != nil {
                fmt.Println(err)
                panic(err)
        }
        db = db.Debug()

        var list1 []map[string]interface{}
        err = db.Clauses(hints.ForceIndex("IDX_login")).Table("my_table").Select("login, username").Where("login = ?", 123456).Find(&list1).Error
        fmt.Println(err)
        fmt.Println(list1)
}

And the snapshot of its result

[rts-gordon]

@ivila I have fixed this issue with your help. Thank you very much.