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
