List:General Discussion« Previous MessageNext Message »
From:Rudy Metzger Date:July 9 2003 3:47pm
Subject:RE: Can mysql handle this load?
View as plain text  
Mike,

#2 is not 100% correct. Only the columns in the "lookup" table (table2) need to be
indexed. Why? The optimizer first gets a limited set of table1 and then checks if it can
join this subset with a key from table2. So for the join condition only table2 needs to
be indexed.

However, you are right that in most of the cases you also want to have a subset out of
table one first. For that you also should use an index, but in most cases this will be a
different index.

Example:

CREATE TABLE table1 (
  A    INT NOT NULL,
  B    INT,
  C    INT,
PRIMARY KEY(A),
UNIQUE INDEX uidx1(B)
);

CREATE TABLE table2 (
  C    INT NOT NULL,
  D    INT,
PRIMARY KEY(C)
);

SELECT table2.D
  FROM table1, table2
 WHERE table1.B = 25
   AND table1.C = table2.C

The optimizer now would first find all rows from table1 having B = 25 by using the INDEX
uidx1 and then join table 2 via the columns table1.C = table2.C using the index PRIMARY
KEY from table2.

So for the JOIN you only need to have table2 indexed, there is no need to put an index on
the column C on table1. BTW you can always see what the optimizer plans to do by setting
EXPLAIN right before SELECT (EXPLAIN SELECT ...)

What the former post was more about is REFERENTIAL INTEGRITY, which is something that is
not (yet) include in MySQL (at least for MyISAM, for InnoDB it is coming with 5.0 I
think).

Cheers
/rudy

-----Original Message-----
From: Mike Hillyer [mailto:mhillyer@stripped] 
Sent: woensdag 9 juli 2003 17:19
To: Adam Gerson; nospam@stripped
Cc: mysql@stripped; benchmarks@stripped
Subject: RE: Can mysql handle this load?

Well that all depends. The real reason for a primary key is to prevent duplicates,
therefore, if a combination of fields needs to be unique, then a multiple primary key
makes sense, especially if other tables will reference the field combination (for
example, detail items on an invoice where the invoice number will not be unique, and the
detail number will not be unique, but the combination of the two will be).

As for question #2, if two fields, one on each table, will be used to join tables
together, both fields should be indexed.

Regards,
Mike Hillyer
www.vbmysql.com


> -----Original Message-----
> From: Adam Gerson [mailto:agersonl@stripped] 
> Sent: Wednesday, July 09, 2003 9:09 AM
> To: nospam@stripped
> Cc: mysql@stripped; benchmarks@stripped
> Subject: Re: Can mysql handle this load?
> 
> 
> Is it better to set multiple primary keys or to set one key and index 
> the other columns? If I have a primary key as a field in 
> another table 
> should it also be set as a key?
> 
> 
> 
> Adam
> 
> On Wednesday, July 9, 2003, at 10:41 AM, 
> <nospam@stripped> wrote:
> 
> > i think this should be no problem...
> >
> > i'd think of some table layout like this:
> > date         int      PRIMARY
> > student_id   int      PRIMARY
> > status       int
> > extra_data   what-you-want
> >
> > then you should get about 360,000 records per year.
> > i saw people on this list reporting about millions of 
> records etc... 
> > and i guess they had a little greater tables than you 
> should get here.
> >
> > but why would you want to move any previous records to 
> another table 
> > all the time? just keep it in one table and back up anything older 
> > than 5 years or so. that keeps your table at, say 50 MB, 
> and you can 
> > run real-time queries anytime :)
> >
> > -yves
> >
> >
> > -----Ursprüngliche Nachricht-----
> > Von: "Adam Gerson" <agersonl@stripped>
> > An: <mysql@stripped>
> > Cc: <benchmarks@stripped>
> > Gesendet: Mittwoch, 9. Juli 2003 15:46
> > Betreff: Can mysql handle this load?
> >
> >
> >> I am writing an attendance system in php for my school. We have a
> >> little less then 1000 students. For every day of the 
> school year one
> >> record will be entered into a table for each student representing 
> >> their
> >> attendance status (present, absent, late, etc...). I also 
> have several
> >> other supporting tables for relationships. When it comes 
> to reporting
> >> and querying this DB I am worried that it will very quickly become 
> >> very
> >> large and slow. Can mysql handle this? Are there any techniques to
> >> speed it up? I will trying indexing major columns.
> >>
> >> I have also considered keeping all previous days attendance in a
> >> separate table from the current days attendance and moving 
> things over
> >> in the middle of the night. This way any operations on the current 
> >> days
> >> data will go quickly, but reports on long term things will still be
> >> slow. Good idea?
> >>
> >> Thanks,
> >> Adam
> >>
> >>
> >>
> >>
> >>
> >> -------------------------------
> >> Adam Gerson
> >> Systems Administrator / Computer Teacher
> >> Columbia Grammar and Prep School
> >> 212-749-6200
> >> agerson@stripped
> >> www.cgps.org
> >>
> >>
> >> -- 
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:    
> >> http://lists.mysql.com/mysql?unsub=1
> >>
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?> unsub=mhillyer@stripped
> 
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Access deniedAsterix11 Mar
  • Re: Access deniedChristian Mack11 Mar
  • Re: Can mysql handle this load?nospam9 Jul
  • RE: Can mysql handle this load?Andy Eastham9 Jul
  • Re: Can mysql handle this load?Krasimir_Slaveykov10 Jul
  • Left Join - Revistedvernon10 Jul
RE: Can mysql handle this load?Mike Hillyer9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: order of table joins or where clauses relevant?Rudy Metzger10 Jul
RE: Left Join - RevistedRudy Metzger11 Jul
RE: Can mysql handle this load?Rudy Metzger14 Jul