FML I'm losing it

0.intro

Somehow this query is 5seconds the first time I run it and 0.000smtsmall seconds the 2nd time, even if do SELECT SQL_NO_CACHE

I already tried optimizing / repairing the tables, even completely dropped and recreated the database ...

1.create.sql

CREATE TABLE `sell_listing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `listing_date` date NOT NULL,
  `listing_time` time NOT NULL,
  `item_id` int(11) NOT NULL,
  `listings` int(11) NOT NULL,
  `unit_price` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `retrieve_by_date_time` (`item_id`,`listing_date`,`listing_time`)
) ENGINE=MyISAM AUTO_INCREMENT=13466429 DEFAULT CHARSET=latin1

2.select.sql

SET @ITEMID=15749; 
SELECT 
MIN(unit_price) AS min_unit_price, 
sell_listing.LISTING_DATE AS "listingDate", 
sell_listing.LISTING_TIME AS "listingTime" 
FROM `sell_listing` 
WHERE sell_listing.ITEM_ID=@ITEMID 
GROUP BY sell_listing.LISTING_DATE, sell_listing.LISTING_TIME;

3.explain

+----+-------------+--------------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table        | type | possible_keys         | key                   | key_len | ref   | rows | Extra       |
+----+-------------+--------------+------+-----------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | sell_listing | ref  | retrieve_by_date_time | retrieve_by_date_time | 4       | const |  760 | Using where |
+----+-------------+--------------+------+-----------------------+-----------------------+---------+-------+------+-------------+

4.indexes

+--------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| sell_listing |          0 | PRIMARY               |            1 | id           | A         |    13466428 |     NULL | NULL   |      | BTREE      |         |
| sell_listing |          1 | retrieve_by_date_time |            1 | item_id      | A         |       18100 |     NULL | NULL   |      | BTREE      |         |
| sell_listing |          1 | retrieve_by_date_time |            2 | listing_date | A         |      336660 |     NULL | NULL   |      | BTREE      |         |
| sell_listing |          1 | retrieve_by_date_time |            3 | listing_time | A         |    13466428 |     NULL | NULL   |      | BTREE      |         |
+--------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+

5. mysqlstatus

MySQL 5.1.63-0ubuntu0.1  uptime 0 0:15:7        Thu Sep 20 19:18:21 2012

__ Key _________________________________________________________________
Buffer used     1.70M of 128.00M  %Used:   1.33
  Current      25.04M            %Usage:  19.56
Write hit       0.00%
Read hit       90.18%

__ Questions ___________________________________________________________
Total             776     0.9/s
  DMS             340     0.4/s  %Total:  43.81
  QC Hits         243     0.3/s           31.31
  COM_QUIT        182     0.2/s           23.45
  Com_             13     0.0/s            1.68
  -Unknown          2     0.0/s            0.26
Slow 1 s           17     0.0/s            2.19  %DMS:   5.00  Log:  ON
DMS               340     0.4/s           43.81
  SELECT          340     0.4/s           43.81        100.00
  REPLACE           0       0/s            0.00          0.00
  DELETE            0       0/s            0.00          0.00
  INSERT            0       0/s            0.00          0.00
  UPDATE            0       0/s            0.00          0.00
Com_               13     0.0/s            1.68
  show_variab       6     0.0/s            0.77
  show_status       3     0.0/s            0.39
  set_option        3     0.0/s            0.39

__ SELECT and Sort _____________________________________________________
Scan               80     0.1/s %SELECT:  23.53
Range              22     0.0/s            6.47
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan          89     0.1/s
Sort range         18     0.0/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage    3.07M of  16.00M  %Used:  19.20
Block Fragmnt   0.20%
Hits              243     0.3/s
Inserts           249     0.3/s
Insrt:Prune     249:1     0.3/s
Hit:Insert     0.98:1

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate         305     0.3/s

__ Tables ______________________________________________________________
Open               38 of  256    %Cache:  14.84
Opened            156     0.2/s

__ Connections _________________________________________________________
Max used            3 of  200      %Max:   1.50
Total             184     0.2/s

__ Created Temp ________________________________________________________
Disk table         48     0.1/s
Table             215     0.2/s    Size:  16.0M
File                5     0.0/s

__ Threads _____________________________________________________________
Running             1 of    1
Cached              2 of    8      %Hit:  98.37
Created             3     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             0       0/s
Connects            1     0.0/s

__ Bytes _______________________________________________________________
Sent            3.18M    3.5k/s
Received      120.99k   133.4/s

__ InnoDB Buffer Pool __________________________________________________
Usage         304.00k of   8.00M  %Used:   3.71
Read hit       94.29%
Pages
  Free            493            %Total:  96.29
  Data             19                      3.71 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads             210     0.2/s
  From file        12     0.0/s            5.71
  Ahead Rnd         1     0.0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads            25     0.0/s
  Writes            3     0.0/s
  fsync             3     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read             19     0.0/s
  Written           0       0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s

6. filesizes

$ du -h /var/lib/mysql/gw2spidy/*.MYI
167M	/var/lib/mysql/gw2spidy/buy_listing.MYI
4.0K	/var/lib/mysql/gw2spidy/gem_exchange.MYI
44K	/var/lib/mysql/gw2spidy/gem_to_gold_rate.MYI
44K	/var/lib/mysql/gw2spidy/gold_to_gem_rate.MYI
4.0K	/var/lib/mysql/gw2spidy/gw2session.MYI
592K	/var/lib/mysql/gw2spidy/item.MYI
4.0K	/var/lib/mysql/gw2spidy/item_sub_type.MYI
4.0K	/var/lib/mysql/gw2spidy/item_type.MYI
263M	/var/lib/mysql/gw2spidy/sell_listing.MYI


$ du -h /var/lib/mysql/gw2spidy/*.MYD
168M	/var/lib/mysql/gw2spidy/buy_listing.MYD
4.0K	/var/lib/mysql/gw2spidy/gem_exchange.MYD
64K	/var/lib/mysql/gw2spidy/gem_to_gold_rate.MYD
64K	/var/lib/mysql/gw2spidy/gold_to_gem_rate.MYD
4.0K	/var/lib/mysql/gw2spidy/gw2session.MYD
2.5M	/var/lib/mysql/gw2spidy/item.MYD
4.0K	/var/lib/mysql/gw2spidy/item_sub_type.MYD
4.0K	/var/lib/mysql/gw2spidy/item_type.MYD
348M	/var/lib/mysql/gw2spidy/sell_listing.MYD

The time to load the table from the hard drive for the first query ?

Oh god, I had similar problems like 2 years ago. Can't remember the solution. I know it was something stupid with mysql. Maybe something to do with explicitly defining what key you want to use(in the select query, try that)

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html for some documentation :)

Also: create an index on something maybe? Just fiddle around with the query a bit, mysql can be very weird. Another thing that could help:
explain extended ;
show warnings;
Then show warnings returns you the query as MySQL interpreted it, might be of use if you try that the first time and the second time.

I'm no MySQL expert but perhaps add unit_price to your index. See here: http://mysqldba.blogspot.com.au/2008/06/how-to-pick-indexes-for-order-by-and.html and http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html

I tried to add a comment but not sure if it undergoes moderation or something so trying again. I'm no MySQL expert but based on http://mysqldba.blogspot.com.au/2008/06/how-to-pick-indexes-for-order-by-and.html I would suggest adding unit_price to your retrieve_by_date_time index and see if that helps.