Hello.
> Or something else entirely? I'd guess the latter is the > Right Way, since the index will be significantly smaller, > but then I still need to go to the table to retrieve foo_id. What is important for you - speed or space? 5-6 million rows for table with this structure won't take much space - not more then several hundreds megabytes by my calculations, so the speed may be that you want. According to: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Queries like "select user_id, foo_id from user_foos where user_id > 2" can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed. Eamon Daly wrote: > I don't know why I have so much trouble visualizing indexes, > but such is life. Imagine a simple table with only two > columns: user_id and foo_id. I will only ever be looking up > records in this table by user_id, but I will always retrieve > both fields. Figure a million rows and maybe 5 or so rows > per user_id. > > Should the table be defined with a PK across all columns: > > CREATE TABLE `user_foos` ( > `user_id` int(10) unsigned NOT NULL default '0', > `foo_id` int(10) unsigned NOT NULL default '0', > PRIMARY KEY (`user_id`,`foo_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > or should the table have no PK and a key on user_id: > > CREATE TABLE `user_foos` ( > `user_id` int(10) unsigned NOT NULL default '0', > `foo_id` int(10) unsigned NOT NULL default '0', > KEY `user_id_idx` (`user_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > Or something else entirely? I'd guess the latter is the > Right Way, since the index will be significantly smaller, > but then I still need to go to the table to retrieve foo_id. > > ____________________________________________________________ > Eamon Daly > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
