MySQL Parameterized Query execute time too long

[MakeEarthBetter]

When I execute a SQL like:

select age from table1 where mobile in (?,?,?......); // about 4k '?' here

The time for execute will be so so so long , maybe 8 seconds.

However, if I splice SQL dicrectly, it will be 0.5s to execute. Like: select age from table1 where mobile in (1,2,3,4,5...);

But I really want to use Parameterized Query for safe. There is another question : the quantity of '?' is not certain.

Has anyone encountered this problem?

I need your help plz

[ivila]

@MakeEarthBetter Is there any index on your mobile field? If so, then that your situation could due to that when using PrepareStatement(your Parameterized Query), sometimes MySQL would use wrong indexes or ignore some indexes you think it should. Try change your sql to:

# assume you have a index idx_mobile of your mobile field
select age from table1 FORCE INDEX(idx_mobile) where mobile in (?,?,?......);

Just don't rely too much on MySQL query optimizer, there are many edge cases it can't handle.

[MakeEarthBetter]

@MakeEarthBetter Is there any index on your mobile field? If so, then that your situation could due to that when using PrepareStatement(your Parameterized Query), sometimes MySQL would use wrong indexes or ignore some indexes you think it should. Try change your sql to:

# assume you have a index idx_mobile of your mobile field
select age from table1 FORCE INDEX(idx_mobile) where mobile in (?,?,?......);

Just don't rely too much on MySQL query optimizer, there are many edge cases it can't handle.

Thanks for your solution so much and It has decreased the time to 2.5 seconds! But another question appeared: 1: model.DB.Raw(select age from table1 where mobile in (1,2,3,4,5...);) // time:0.04s 2: model.DB.Raw(select age from table1 where mobile in (?,?,?,?,?...);, paramList...) // time:2.5s 3: execute in MySQL Client: select age from table1 where mobile in (1,2,3,4,5...); // time:1.0s

It really puzzled me. Do you know what may makes that difference?

[ivila]

You can just open the profile settings in your mysql server to see what happened, check this. I guess:

1. the 0.04s: there are cache in your server or client, or there are mistakes in your codes.
2. the 2.5s: the cost of PrepareStatement.
3. the 1.0s: what it really should be.

But as what I said, just open the profile settings in your mysql server to see what happened.