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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
