You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
@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 fieldselect 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 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 fieldselect 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?
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
The text was updated successfully, but these errors were encountered: