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
