Page 1 of 1

INDEX using

Posted: Wed Sep 28, 2005 2:34 am
by sebs
Is there an advantage in making 8 indexes on different columns,because when I try to make one for all the columns I get:
max key length is 1000 bytes.I can't even make on index on 2 columns because I get the same error.
I ask you because I haven't found a doc for this case.

Posted: Wed Sep 28, 2005 3:42 am
by CoderGoblin
Trying to add indexes to 8 columns should not be necessary. You will in all likelyhood actually slow the system down.

Indexes are best used on integer columns. The primary key (the thing you normally search on being the most important).

index

Posted: Wed Sep 28, 2005 4:22 am
by sebs
The problem is I search for all the 8 columns.I don't even use a search after the primary key

Posted: Wed Sep 28, 2005 4:58 am
by CoderGoblin
Can you give an example structure for your table. It may be better to split the table up into two or more to provide quicker responses.

example

Posted: Wed Sep 28, 2005 5:09 am
by sebs
ID,Name,CODE,macro,micro,City,Provincia,Region,Website,Telefon,Telefon1,Fax,Fax1,Adress,Logo,Code1 email,email1and some fields I don't use
all varchar(255) except ID,CODE which are float
The 8 columns I search for are:
Name,Code,macro,micro,City,Provincia,Region,Code1

Posted: Wed Sep 28, 2005 11:33 am
by Skara
Ok, think about the max size of a telephone number. Usually something like... "+1 234 567 8901." That's 15 characters... If someone wrote it (oddly) like this: "+1 - (234) - 567 - 8901," that's still just 23 characters. There's no reason you should have it at 255. Ditto for all your fields. When the user submits the information, just do a quick check to see how long it is. If someone enters a phone number that's 100 characters long, there's something really fishy going on.
Also, why is ID a float? float is for decimal places. Use an integer. I don't know about the CODE field, but it should be the same.
In fact, it might be best to use an integer for the phone number too. Just take out everything but digits when the user submits it.

change varchar

Posted: Thu Sep 29, 2005 3:06 am
by sebs
and after that I will be able to make an index on the 8 columns or it will just work fast the sqlquery from the database?
Thanks!