create index using substr

[c-chan]

---

possible ba nga maka create ko ug index using substr

for example nakoi field nga num nga naai char( as the datatype and length.

ang value sa num would look something like 09092010

now, ganahan nako himuon nga index ang num nga field. pero ang gusto nako ang last 4 characters lang. bale ang ma index lang kai ang 2010.

possible bana?

thank you.

[stjohnmedrano]

possible ba nga maka create ko ug index using substr

for example nakoi field nga num nga naai char( as the datatype and length.

ang value sa num would look something like 09092010

now, ganahan nako himuon nga index ang num nga field. pero ang gusto nako ang last 4 characters lang. bale ang ma index lang kai ang 2010.

possible bana?

thank you.

Maybe its possible, try daw use Array.

[dodie]

Array try it.

the index would be around -4 or -3 subtracting to the maximum number of characters.

[c-chan]

ahmm ako gusto is something like this

create index nmbr on nmbr_table (substr(nmbr,1,2));


possible man daw ang ing ana sa oracle. but di ko kabalo unsaun na siya sa mysql >.<

no loop involve. just plain sql queries. possible?

thank you

[dodie]

ahmm ako gusto is something like this

create index nmbr on nmbr_table (substr(nmbr,1,2));


possible man daw ang ing ana sa oracle. but di ko kabalo unsaun na siya sa mysql >.<

no loop involve. just plain sql queries. possible?

thank you

Please read the this link: MySQL :: MySQL 5.0 Reference Manual :: 12.1.8 CREATE INDEX Syntax

I'll explain some for your topic.
Creating index is only to perform faster searching capability. Although it has to consume space to store indexes. The index is only from left to right range, and the differnces of CARDINALITY.

Just imagine an ordinary book..... with indexes / words list / at the end part of the book.

When you assign a value for the index... you only assign value for VARCHAR, the rest of the datatypes are OK. You don't have to think much about it, just observe how currently fast the table is. If it's slow, then recreate the index again with higher value.... OPTIMIZE table again.