Help creating index's for this query...
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]
Re: Help creating index's for this query...
Cory at SkyVantage wrote: 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 You are using indicies here, so I don't understand what you are asking. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
gerald_clark wrote: Cory at SkyVantage wrote: *** 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 using indicies here, so I don't understand what you are asking. There are no indexes used on the above. Right? or am I missing something? (the joins are using the pk index's, but the WHERE part of the statement isn't, because there are no index's on those fields.) Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
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
Re: Help creating index's for this query...
Cory at SkyVantage wrote: gerald_clark wrote: Cory at SkyVantage wrote: *** 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 using indicies here, so I don't understand what you are asking. There are no indexes used on the above. Right? or am I missing something? (the joins are using the pk index's, but the WHERE part of the statement isn't, because there are no index's on those fields.) Cory How many possible values may res_status have? What percentage for each value? It may not be possible to use an index on this field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
[EMAIL PROTECTED] wrote: Can you post the results of SHOW CREATE TABLE pnr_segments, please pnr_segments | CREATE TABLE `pnr_segments` ( `ID` bigint(20) NOT NULL auto_increment, `ID_pnr` bigint(20) NOT NULL, `ID_pnr_fares` bigint(20) NOT NULL, `flight_number` int(10) unsigned NOT NULL, `flight_date` date NOT NULL, `origin` char(3) NOT NULL, `destination` char(3) NOT NULL, `weightedpercent` decimal(9,6) NOT NULL, `intended_origin` char(3) NOT NULL, `intended_destination` char(3) NOT NULL, PRIMARY KEY (`ID`,`ID_pnr`), ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
gerald_clark wrote: How many possible values may res_status have? What percentage for each value? It may not be possible to use an index on this field. The res_status field may have up to 10 different values from 0-9. and there will be a huge amounts of '0' and thousands of records with 1-9 (most likely ~70% '0's) Cory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
Cory at SkyVantage wrote: gerald_clark wrote: How many possible values may res_status have? What percentage for each value? It may not be possible to use an index on this field. The res_status field may have up to 10 different values from 0-9. and there will be a huge amounts of '0' and thousands of records with 1-9 (most likely ~70% '0's) Cory Then a full table scan is faster than using an index for res_status=0. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
Cory at SkyVantage [EMAIL PROTECTED] wrote on 03/10/2006 04:25:00 PM: [EMAIL PROTECTED] wrote: Can you post the results of SHOW CREATE TABLE pnr_segments, please pnr_segments | CREATE TABLE `pnr_segments` ( `ID` bigint(20) NOT NULL auto_increment, `ID_pnr` bigint(20) NOT NULL, `ID_pnr_fares` bigint(20) NOT NULL, `flight_number` int(10) unsigned NOT NULL, `flight_date` date NOT NULL, `origin` char(3) NOT NULL, `destination` char(3) NOT NULL, `weightedpercent` decimal(9,6) NOT NULL, `intended_origin` char(3) NOT NULL, `intended_destination` char(3) NOT NULL, PRIMARY KEY (`ID`,`ID_pnr`), ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 Try this: ALTER TABLE pnr_segments ADD KEY(`origin`,`destination`,`flight_date`,`flight_number`,`id`); and this modified query: SELECT COUNT(1) Count FROM pnr_passengers pax INNER JOIN pnr_status status ON pax.ID = status.ID_passengers INNER JOIN pnr_segments ps ON status.ID_segments = ps.ID AND ps.origin='SJU' AND ps.destination='SIG' AND ps.flight_date='2006-03-10' AND ps.flight_number='218' WHERE status.res_status='0'; This is a rather extreme optimization (adding such a large index) but you did say this was a very frequently run query so the performance boost may offset any additional data load. Please tell me you aren't making the database do all of this work just to run the exact same query in the very next statement with a different SELECT clause in order to pull data from these same tables That would be silly :-( If you have a real query (one that doesn't just count how many rows you find) based on these same tables and conditions and you running this query as a test to say if count==0 then skip the real query then you are seriously wasting a trip to the database and a lot of CPU cycles while you are there. Ask for what you need the first time and check to see if you get any results. Then move on if you don't have any. Now, you may have simplified the SELECT clause to obfuscate the column names you actually have in the tables, that I completely understand. However, if you really do this as a query, you should stop. A further refinement to this index would be to relist the columns in the order in which they are found in WHERE clauses most frequently (still leaving the ID column at the end of the list). This is an example of a covering query and when done correctly, they can seriously improve performance for entire categories of queries. In fact, you may consider adding more columns to the list if you use other things than just the ID value in your SELECT clause... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
