Create a table like this:
CREATE TABLE test ( a INT(10), b char(10));
add two indexes:
alter table test add key aa (a), add key bb (b);
insert some rows:
insert into test values (132,'logi');
insert into test values (322,'koko');
insert into test values (32,'kola');
etc...
You can test with a little number of rows BUT in my application i will have a hundred million of rows so what i am going to ask makes sense: So, let's say that i want to execute two queries:
1) explain select * from test where b like "frif%";
2) explain select * from test where a like "32%";
The first uses the index bb since b is a char, while the second doesn't use the index aa since a is just an INT (and i treat it like a char!). How can i make the second query execute as the first and search for numbers like "some_number%" and make that a query of range type and not ALL (as explain shows).
If you are treating the number as a character string, then store it that way. It sounds like the number is a code of some sort. Although consisting of numbers, it is really just the name of something (such as an account number).
If the number is fixed length, say 5, then you can do:
where a >= 32000 and a < 33000;
You could extend this idea for different lengths:
where a >= 32 and a < 33 or
a >= 320 and a < 330 or
a >= 3200 and a < 3300 or
a >= 30000 and a < 33000
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments