Cory at SkyVantage <[EMAIL PROTECTED]> wrote on 03/10/2006 03:52:56 PM:
> I need help, I'm somewhat new to indexing, I am joining two tables and > need to optimize the speed of the query. I'm running the NDB storage > engine so the foreign key stuff is disabled. With that in mind, here's > the query and additional comments below: > > SELECT COUNT(*) Count FROM pnr_passengers pax INNER JOIN pnr_status > status ON pax.ID = status.ID_passengers INNER JOIN pnr_seg > ments ps ON status.ID_segments = ps.ID WHERE status.res_status='0' AND > ps.origin='SJU' AND ps.destination='SIG' AND ps.flight_ > date='2006-03-10' AND ps.flight_number='218'; > > Field types > pax.ID, bigint (keyfield, indexed) > status.ID, bigint (keyfield, indexed) > ps.ID, bigint (keyfield, indexed) > status.res_status, int > ps.origin, char(3) > ps.destination, char(3) > ps.flight_date, date > ps.flight_number, int > > As you can see I'm joining three tables here each having a one-to-many > relationship with each other > pnr_passengers -one to many with- pnr_status > pnr_status -one to many with- pnr_segments > > Here's an EXPLAIN with \G: > > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: status > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 100 > Extra: Using where > *************************** 2. row *************************** > id: 1 > select_type: SIMPLE > table: pax > type: ref > possible_keys: PRIMARY > key: PRIMARY > key_len: 8 > ref: W4_AirlineData.status.ID_passengers > rows: 1 > Extra: > *************************** 3. row *************************** > id: 1 > select_type: SIMPLE > table: ps > type: ref > possible_keys: PRIMARY > key: PRIMARY > key_len: 8 > ref: W4_AirlineData.status.ID_segments > rows: 1 > Extra: Using where > > > Any help you anyone can provide as to the best way to create these > index's that'd be great! This is an OFTEN used query in our software > and the tables with have many rows. > > Cory > Can you post the results of "SHOW CREATE TABLE pnr_segments", please? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
