Baron Schwartz <[EMAIL PROTECTED]> wrote: 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] Thank you Baron for your kind suggestion. Guess I have to bite the bullet and do the indexing based on your suggestion. Thanks again. --------------------------------- Ahhh...imagining that irresistible "new car" smell? Check outnew cars at Yahoo! Autos.
