murthy gandikota wrote:
Hi,
I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column?
BTW, here is the scenario:
create table `test` (`phonenumber` varchar(100));
insert into test (phonenumber) values(`0001234567');
and so on 2 million times
alter table test add index (phonenumber)
An index is probably beneficial. You can find the cardinality of the
proposed index (number of distinct values the column has) with COUNT(*)
queries. If you divide this and the number of rows in the table, you
get the index's selectivity. In general you want a selectivity greater
than 1/3 for the index to be useful. Higher is better, all other things
being equal.
Supposing you have 2 million records,
SELECT COUNT(DISTINCT phonenumber)/2000000 AS selectivity FROM test;
Now, you may be able to index just a prefix of the column and still get
as much or almost as much selectivity. Try this:
SELECT COUNT(DISTINCT LEFT(phonenumber,5))/2000000 AS selectivity FROM test;
Play with the number of characters until you find a good balance between
the selectivity and the space the index will take. The smaller the
prefix, the smaller the index. In the end you will have to profile the
queries you're going to run to figure out exactly what will be the best
for your situation.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
