"Scott Purcell" <[EMAIL PROTECTED]> wrote on 28/06/2005 14:36:37:
> Hello, > I am reading the docs, but I am slightly confused. > > I have a table with a varchar(50) column (not a primary column) > where I do not want duplicates. It is a properties column, and I am > getting duplicates inserted, which is causing problems in my display. > > An Oracle DBA that works with me suggested creating a unique index > on the column. I am reading the docs here: > http://dev.mysql.com/doc/mysql/en/create-index.html > but I am not have a clear understanding of an index, so I am having > trouble visualizing what I need to do. The column already exists. Your DBA is correct: you need to add a UNIQUE index onto the column. MySQL has no way of knowing that you want a column to be unique unless you tell it so. And if you want it to be unique, you have to create an index so that MySQL can do a fast lookup to see if the column already exists before adding a new one. If you did not have an index, MySQL would have to search the entire table to check for duplicates on each insert - an intolerably slow operation. So you need a UNIQUE index. It is very easy to add an index to an existing table: ALTER TABLE properties ADD UNIQUE(property) ; but you must get rid of the duplicates first - it cannot create a UNIQUE index where duplicates exist. Also, when you make a column unique, you must consider what the software that inserts records is to do if it encounters a duplicate. You may need, for example, to convert your INSERT commands into REPLACE (see manual). > I am running 4.0.15 on a PC. The current column type is: MyISAM. I > am not sure if that is proper or not. Its usage is for a web-site. > > Here is what I created a while back: > CREATE TABLE PROPERTIES ( > property varchar(50), > value varchar(200), > description varchar(200) > ) TYPE=MyISAM; > > Also, if this is doable, can I also create an index across two > columns? I have another situation where I need a combination of two > columns to be unique. Yes, you can - and it is the correct thing to do in this case. ALTER TABLE my_table ADD UNIQUE index_name (col_1, col_2) ; If, as you say, you do not have a clear visualisation of an index, I suggest that you should attempt to acquire one fast. Indexing is absolutely central to database programming and no-one should be writing database access software without understanding it. The concept is not very complex. You are probably familiar with indexes in the back of reference books: a list of words drawn from the text of the book is listed in alphabetic order, and each entry gives the page number(s) upon which you find those words. So that if you want to find a word, look it up in the index and then turn straight to the right pages, rather than having to read the entire book to find the reference you want. A database index is the same, except that it indexes every "word" in a particular column, and the "page number" it looks up is the databases hidden internal reference to the record which contains the indexed "word" (or number, or date, or ...). The marvellous thing is that you just tell MySQL you want an index, and MySQL magically creates and maintains the index, then uses it when appropriate to speed up database searches. Of course, maintaining an index takes extra CPU and disk power, so the database will not build an index unless you ask for it, which you should only do for columns used in WHERE clauses. But once created, the rest happens by "magic" (or rather, courtesy of the skills of the MySQL engineers). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
