Lourdes, ----- Original Message ----- From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, October 11, 2002 11:38 PM Subject: InnoDB indexing questions
> 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? no. It checks which clustered index columns already are in the secondary index definition, and only adds the missing ones internally. You can use innodb_table_monitor to look at the internal structure of table definitions. > 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? It is the task of the MySQL optimizer to decide these. At least in some cases the optimizer is aware that the columns of the primary key (= clustered index in InnoDB) are stored in every secondary index record. But I guess in some cases the optimizer does not remember to use this information. Thus, in some cases it may be a good idea in a CREATE TABLE to add primary key columns explicitly after the secondary key columns in a secondary index definition. That makes it certain that the MySQL optimizer remembers the columns are physically there. But do not break your UNIQUE secondary indexes by adding extra columns. > 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. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query --------------------------------------------------------------------- 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
