MySQL Forums
Forum List  »  Performance

Re: How MySQL Uses Indexes
Posted by: Mola Ji
Date: May 07, 2013 12:24PM

OK,
The structure:
CREATE TABLE IF NOT EXISTS `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`i` date NOT NULL,
`o` date NOT NULL,
PRIMARY KEY (`id`),
KEY `a` (`o`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The Request:
SELECT i, o
FROM `t`
WHERE o > '2013-04-05'
AND i < '2013-04-11'

I test the request with two tables

First table:
4269 records with o above '2013-04-05'
6930 records with o less than '2013-04-05'
total records: 11199
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 10929
Extra: Using where

Second table:
1269 records with o above '2013-04-05'
6930 records with o less than '2013-04-05'
total records: 8199

The result:
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: a
key: a
key_len: 3
ref: NULL
rows: 1267
Extra: Using where

Where records above '2013-04-05' is important, mysql does not use index?

if I use FORCE INDEX(a) with the first table, I have the result below:
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: a
key: a
key_len: 3
ref: NULL
rows: 5655
Extra: Using where

In my table, usually record are distributed so that 70% come before the date 'o'

test online: http://internet-setup.net/mysql_perf.php

Options: ReplyQuote


Subject
Views
Written By
Posted
2231
May 04, 2013 08:28AM
919
May 06, 2013 10:31AM
945
May 06, 2013 07:57PM
Re: How MySQL Uses Indexes
926
May 07, 2013 12:24PM
831
May 07, 2013 07:47PM
827
May 08, 2013 05:33AM
837
May 08, 2013 09:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.