Query Optimization

[ballhogjoni]

Can anyone think of a way to optimize this query?:

 

SELECT sum(o.total) as gross_sales FROM travel_orders AS o WHERE o.company_id != '0' AND (o.charge_date BETWEEN '1199163600' AND '1230699600') AND o.status IN ('charge_held','charged','filled','in_process','processed')

 

I am using explain and my type is ALL, so I want to get it to use something else.

 

Thanks

[corbin]

What indexes do you have on the table?

 

 

Can you show us:

 

SHOW CREATE TABLE <table name>;

 

and

 

 

EXPLAIN SELECT sum(o.total) as gross_sales FROM travel_orders AS o WHERE o.company_id != '0' AND (o.charge_date BETWEEN '1199163600' AND '1230699600') AND o.status IN ('charge_held','charged','filled','in_process','processed');

 

 

First thing that comes to mind is no indexes.  Second thought is a full table scan on SUM(o.total) (which would be related to no-indexes).  Third is o.status being compared to strings....

[ballhogjoni]

Can you show us:

 

SHOW CREATE TABLE <table name>;

 

CREATE TABLE `travel_orders` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `company_id` int(10) unsigned NOT NULL default '0',

  `status` enum('cc_pending','incomplete','new','cancel','printed','partially_charged','charged','charge_cancel','filled','processed','proc_cancel','held','charge_held') NOT NULL default 'cc_pending',

  `charge_date` int(10) unsigned NOT NULL default '0',

  `total` float NOT NULL default '0',

  PRIMARY KEY  (`id`),

  KEY `order_status` (`status`),

  KEY `company_idx` (`company_id`),

  KEY `charge_date` (`charge_date`)

) ENGINE=MyISAM AUTO_INCREMENT=133023 DEFAULT CHARSET=latin1

 

 

I did not show the entire table or all of the keys because its private, but you now have the relevant info.

 

EXPLAIN:

1,SIMPLE,o,ALL,(order_status,status_affiliate,company_idx,charge_date),N,N,N,651,Using where

 

Each column is comma seperated.

[fenway]

Drop the != condition for a minute... it shouldn't be ALL.

[ballhogjoni]

Drop the != condition for a minute... it shouldn't be ALL.

 

Tried that and it didn't change anything.

[fenway]

Hmmm... I just want to make sure that the indexes are working as expected.  Drop the IN() too... ALL should switch to RANGE.