List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:November 11 2005 12:20am
Subject:Re: PK or simple key?
View as plain text  
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



Thread
PK or simple key?Eamon Daly10 Nov
  • Re: PK or simple key?Gleb Paharenko11 Nov
  • Re: PK or simple key?Jigal van Hemert11 Nov
  • Re: PK or simple key?Martijn Tonies11 Nov
  • Re: PK or simple key?Eamon Daly11 Nov