Hello, I have some doubts about the index behaviour in InnoDB.
I have a table named 'Albaranes'. The definition is as follow: CREATE TABLE `albaranes` ( `Cli` varchar(6) NOT NULL default '', `Alb` varchar(8) NOT NULL default '', . . . PRIMARY KEY (`Alb`), UNIQUE KEY `Cliente` (`Cli`,`Alb`), ) TYPE=InnoDB; The index 'Cliente' is composed of the field 'Cli' and the field 'Alb' which belong to the primary index. I have read in the InnoDB documentation the following text: "The records in non-clustered indexes (we also call them secondary indexes), in InnoDB contain the primary key value for the row. InnoDB uses this primary key value to search for the row from the clustered index." Well, according to the text it wouldn`t be necessary to add the field 'Alb' to the 'Cliente' index to access to a specific record because InnoDB does that. My question is: If I add the field 'Alb' to the 'Cliente' index, does InnoDB add the field 'Alb' too? I have made tests and I have noticed that if I define the index 'Cliente' as (Cli) the time that the query takes in recovering all the table records is more or less the same that the query takes if I define the index 'Cliente' as (Cli,Alb) (there isn't a considerable difference). The query executed is 'Select Cli,Alb From Albaranes where Cli > 'AnyValue''. I'm interesting in recovering the records ordered by Cli,Alb for increasing the speed of some reports so, I executed the query 'Select Cli,Alb From Albaranes where Cli > 'AnyValue' Order by Cli,Alb' and I checked that if I define the index as (Cli) InnoDB doesnīt use the field 'Alb' (which is used to search for the row from the clustered index by InnoDB) to order the selected records. I suppose InnoDB only uses this primary key value to search for the rows and not to order the rows, doesnīt it? My last question is if I add the fields from the clustered index to other secondary indexes (for example the index 'Cliente'), have I to define the secondary index as 'UNIQUE'? I think that itīs not necessary and the only I could obtain is that inserts are slower because InnoDB has to preserve the data uniqueness. Let me ask a question that is not related to InnoDB. I have three primary fields (varchar) in a table and I have some doubts in the way of defining them. What is better, defining three fields and the primary index is formed by all of them or defining only one field which is composed of the concatenation of the three fields and the index is formed only by one field?. I'm always going to search by the first field. Regards, Lourdes. __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
