INDEX using

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

INDEX using

Post 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.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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).
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

index

Post by sebs »

The problem is I search for all the 8 columns.I don't even use a search after the primary key
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

example

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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.
sebs
Forum Commoner
Posts: 97
Joined: Tue Sep 20, 2005 10:13 am

change varchar

Post 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!
Post Reply