MYSQL query optimization - slow ORDER BY, or LIMIT 1

[Matrixkid]

I'm having an issue with a SELECT statement. I find that if I have a single row of data as the result and using ORDER BY it slows it right down. As soon as I either remove the ORDER BY or add another row of data it returns it in reasonable time. My data returned for the most part will have more than 1 result (hence the LIMIT 2) but there are some cases where there is only an individual row and it's stalling things.

 

Profiling
Status  Time
starting    0.000082
checking permissions    0.000007
Opening tables  0.000020
System lock 0.000016
init    0.000043
optimizing  0.000021
statistics  0.000016
preparing   0.000016
executing   0.000004
Sorting result  0.000005
Sending data    15.276259
end 0.000013
query end   0.056590
closing tables  0.000030
freeing items   0.000156
logging slow query  0.000008
logging slow query  0.000007
cleaning up 0.000009

 

Showing rows 0 - 0 (1 total, Query took 15.3337 sec)

SELECT expiration, strike, category, last, volume, stock_price
FROM chains
WHERE option_id =  'AMZN^^120203P00165000'
AND DATE( id ) = DATE( NOW( ) ) 
AND TIME( id ) BETWEEN  '09:30:00' AND  '16:00:00'
ORDER BY id DESC 
LIMIT 2

 

As soon as I insert a new row with the same option_id value and a different id(timestamp) value the results is this:

Showing rows 0 - 1 (2 total, Query took 0.2272 sec)

 

OR if I remove the ORDER BY id DESC it returns a similar timed result, less than a second:

Showing rows 0 - 0 (1 total, Query took 0.5024 sec)

 

The SQL EXPLAIN:

id  select_type table   type    possible_keys  key      key_len ref    rows Extra
1   SIMPLE      chains  index   NULL           PRIMARY  27      NULL   2    Using where

 

Lastly, The table structure:

CREATE TABLE IF NOT EXISTS `chains` (
  `id` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `option_id` varchar(21) NOT NULL,
  `symbol` char(4) NOT NULL,
  `expiration` date NOT NULL,
  `strike` decimal(10,2) NOT NULL,
  `category` char(1) NOT NULL,
  `last` decimal(10,2) NOT NULL,
  `volume` int(50) NOT NULL,
  `openint` int(50) NOT NULL,
  `stock_price` decimal(12,2) NOT NULL,
  `stock_volume` int(20) NOT NULL,
  PRIMARY KEY (`id`,`option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

In the end, im trying to grab the "latest" two results.

 

Thanks ahead of time for the help! It's much appreciated!

[fenway]

I'm confused about your choice of key -- and so is mysql.

[Matrixkid]

Yeah, as I hit the button it occurred to me that it might be better to get rid of the option_id field and use the primary key as:

ID,Symbol,Expiration,Strike, Category

 

But the reason behind my current key is that option_id represents all of the above fields except for ID, and the ID field is not necessarily unique since some entries are at the exact same time.

 

What would you suggest as the key?

 

Here is some test data:

INSERT INTO `chains` (`id`, `symbol`, `expiration`, `strike`, `category`, `last`, `volume`, `openint`, `stock_price`) '
VALUES
('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'C', '0.00', 0, 0, '127.88'),
('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'P', '0.02', 834, 510, '127.88'),
('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'C', '0.02', 981, 0, '127.99'),
('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'P', '0.03', 1098, 510, '127.99');

 

[fenway]

I just noticed your ID is a timestamp? Bad idea.

 

Use a surrogate key -- BIGINT UNSIGNED AUTO_INCREMENT -- as the primary key.

 

Make another column for the timestamp.

[Matrixkid]

Perfect. Definitely was the table structure. Works like it should now.

 

Thanks for the guidance.