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
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
