Mysql index optimization for ordered range query

Question

Mysql index optimization for ordered range query

I am looking for help with some offensive mysql queries that I am currently working with my server. My goal is to show the most expensive ebay stuff with a finite time less than a month ago.

I am using MySQL 5.1.

My query is as follows ("ebay_items" has ~ 350,000 lines):

explain SELECT `ebay_items`.* FROM `ebay_items` 
WHERE (endtime > NOW()-INTERVAL 1 MONTH) ORDER BY price desc\G;

gives:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ebay_items
type: range
possible_keys: endtime
key: endtime
key_len: 9
ref: NULL
rows: 71760
Extra: Using where; Using filesort
1 row in set (0.00 sec)

This request results in an expensive file using lines 71760.

show indexes on ebay_items;

gives (I included only the specified index, "endtime"):

*************************** 7. row ***************************
Table: ebay_items
Non_unique: 1
Key_name: endtime
Seq_in_index: 1
Column_name: endtime
Collation: A
Cardinality: 230697
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment: 
*************************** 8. row ***************************
Table: ebay_items
Non_unique: 1
Key_name: endtime
Seq_in_index: 2
Column_name: price
Collation: A
Cardinality: 230697
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:

Only the “end time” key is used in the composite end time index (end time, price). As far as I know, MySQL will not effectively use a composite index when working with a range query in conjunction with the "order" clause.

- ? ( , ), .

, , , X ebay_items '< 1 ", '< 1 ", '< 1 " .. .

MySQL order, ?

!

Edit: Kohányi Róbert , , . , - . , . , filesort I/O.

, MyISAM. - InnoDB? .

+5

sql database mysql

Will Sulzer 04 . '11 0:08

1

Kohányi Róbert · Answer 1 · 2011-12-04T10:34:21+0000

, MySQL . .

100 000 ( , , ). , gendata.pl, .

$tables = {
  rows => [100000],
  names => ['ebay_items'],
  engines => ['MyISAM'],
  pk => ['int auto_increment']
};

$fields = {
  types => ['datetime', 'int'],
  indexes => [undef]
};

$data = {
  numbers => [
    'tinyint unsigned', 
    'smallint unsigned', 
    'smallint unsigned',
    'mediumint unsigned'
  ],
  temporals => ['datetime']
};

: , MyISAM, , InnoDB. (, MyISAM InnoDB .)

, pk, col_datetime col_int. . .

+---------+----------+------+-----+---------+----------------+
| Field   | Type     | Null | Key | Default | Extra          |
+---------+----------+------+-----+---------+----------------+
| endtime | datetime | YES  | MUL | NULL    |                |
| id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| price   | int(11)  | YES  | MUL | NULL    |                |
+---------+----------+------+-----+---------+----------------+

, .

CREATE INDEX `endtime` ON `ebay_items` (endtime, price);
CREATE INDEX `price` ON `ebay_items` (price, endtime);
CREATE INDEX `endtime_only` ON `ebay_items` (endtime);
CREATE INDEX `price_only` ON `ebay_items` (price);

Query

, .

SELECT `ebay_items`.* 
FROM `ebay_items`  
FORCE INDEX (`endtime|price|endtime_only|price_only`)
WHERE (`endtime` > '2009-01-01' - INTERVAL 1 MONTH) 
ORDER BY `price` DESC

_{( . 2009-01-01 NOW(), , , 2009 .)}

EXPLAIN MyISAM () InnoDB ().

EndTime

           id: 1
  select_type: SIMPLE
        table: ebay_items
         type: range
possible_keys: endtime
          key: endtime
      key_len: 9
          ref: NULL
         rows: 25261
        Extra: Using where; Using filesort

           id: 1
  select_type: SIMPLE
        table: ebay_items
         type: range
possible_keys: endtime
          key: endtime
      key_len: 9
          ref: NULL
         rows: 21026
        Extra: Using where; Using index; Using filesort

           id: 1
  select_type: SIMPLE
        table: ebay_items
         type: index
possible_keys: NULL
          key: price
      key_len: 14
          ref: NULL
         rows: 100000
        Extra: Using where

         id: 1
  select_type: SIMPLE
        table: ebay_items
         type: index
possible_keys: NULL
          key: price
      key_len: 14
          ref: NULL
         rows: 100226
        Extra: Using where; Using index

endtime_only

           id: 1
  select_type: SIMPLE
        table: ebay_items
         type: range
possible_keys: endtime_only
          key: endtime_only
      key_len: 9
          ref: NULL
         rows: 11666
        Extra: Using where; Using filesort

          id: 1
  select_type: SIMPLE
        table: ebay_items
         type: range
possible_keys: endtime_only
          key: endtime_only
      key_len: 9
          ref: NULL
         rows: 21270
        Extra: Using where; Using filesort

price_only

           id: 1
  select_type: SIMPLE
        table: ebay_items
         type: index
possible_keys: NULL
          key: price_only
      key_len: 5
          ref: NULL
         rows: 100000
        Extra: Using where

           id: 1
  select_type: SIMPLE
        table: ebay_items
         type: index
possible_keys: NULL
          key: price_only
      key_len: 5
          ref: NULL
         rows: 100226
        Extra: Using where

, endtime_only , MyISAM InnoDB. , , endtime .

Test

( -) MyISAM InnoDB Java.

static final String J = "jdbc:mysql://127.0.0.1:3306/test?user=root&password=root";
static final String Q = "SELECT * FROM ebay_items FORCE INDEX (endtime_only) WHERE (endtime > '2009-01-01'-INTERVAL 1 MONTH) ORDER BY price desc;";

public static void main(String[] args) throws InterruptedException {
  for (int i = 0; i < 1000; i++)
    try (Connection c = DriverManager.getConnection(J);
        Statement s = c.createStatement()) {
      TimeUnit.MILLISECONDS.sleep(10L);
      s.execute(Q);
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
}