Indexes with [code]< or >[/code] comparisons

8 Message(s) by 4 Author(s) originally posted in mysql discussion

From: spamtheaussie

Date: Friday, October 19, 2007

Hi,
I'm trying to figure out why MySQL won't use my index

(Plural "indices" or "indexes")

	1. <programming> A number used to select an element of a list,
	vector, {array} or other sequence.  Such indices are nearly
	always non-negative integers but see {associative array}.

	2. <database> See {inverted index}.  [Other kinds?]

	3. <World-Wide Web> A {search engine}.

	4. <World-Wide Web> A {subject index}.

	4. <jargon> See {coefficient of X}.

	[{Jargon File}]

	(1997-04-09)

when I do a <
comparison, but will use it when I do an = comparison.
I thought a BTREE index should work for < comparisons?

mysql> create index start_date

<convention, data> A string unique to a time duration of 24
	hours between 2 successive midnights defined by the local time
	zone.  The specific representation of a date will depend on
	which calendar convention is in force; e.g., Gregorian,
	Islamic, Japanese, Chinese, Hebrew etc. as well as local
	ordering conventions such as UK: day/month/year, US:
	month/day/year.

	Inputting and outputting dates on computers is greatly
	complicated by these {localisation} issues which is why they
	tend to operate on dates internally in some unified form such
	as seconds past midnight at the start of the first of January
	1970.

	Many software and hardware representations of dates allow only
	two digits for the year, leading to the {year 2000} problem.

	{Unix manual page}: date(1), ctime(3).

	(1997-07-11)

_index using BTREE on
case_sessions(start_date);
Query OK, 423608 row

<data, database, programming> An {ordered set} of {fields},
	usually stored contiguously.  The term is used with similar
	meaning in several different contexts.  In a file, a "record"
	probably has some fixed length, in contrast to a "line" which
	may have any length and is terminated by some {End Of Line}
	sequence).  A {database} record is also called a "row".  In a
	{spreadsheet} it is always called a "row".  Some programming
	languages use the term to mean a type composed of fields of
	several other types ({C} calls this a "{struct}").

	In all these cases, a record represents an entity with certain
	field values.

	Fields may be of a fixed width ({bits} or {characters}) or
	they may be separated by a {delimiter} character, often
	{comma} ({CSV}) or {HT} ({TSV}).

	In a database the list of values of a given field from all
	records is called a column.

	(2002-03-22)

s affected (12.99 sec)
Records: 423608 Duplicates: 0 Warnings: 0

mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00';
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
| id | select_type

<theory, programming> (Or "data type") A set of values from
	which a {variable}, {constant}, {function}, or other
	{expression} may take its value.  A type is a classification
	of data that tells the {compiler} or {interpreter} how the
	programmer intends to use it.  For example, the process and
	result of adding two variables differs greatly according to
	whether they are integers, floating point numbers, or strings.

	Types supported by most programming languages include
	{integers} (usually limited to some range so they will fit in
	one {word} of storage), {Booleans}, {floating point numbers},
	and characters.  {Strings} are also common, and are
	represented as {lists} of characters in some languages.

	If s and t are types, then so is s -> t, the type of
	{functions} from s to t; that is, give them a term of type s,
	functions of type s -> t will return a term of type t.

	Some types are {primitive} - built-in to the language, with no
	visible internal structure - e.g. Boolean; others are
	composite - constructed from one or more other types (of
	either kind) - e.g. {lists}, {arrays}, {structures}, {unions}.
	{Object-oriented programming} extends this with {classes}
	which encapsulate both the structure of a type and the
	operations that can be performed on it.

	Some languages provide {strong typing}, others allow {implicit
	type conversion} and/or {explicit type conversion}.

	(2003-12-22)

| table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

| type | possible_key

1. <database> A value used to identify a {record} in a
	database, derived by applying some fixed function to the
	record.  The key is often simply one of the {fields} (a
	{column} if the database is considered as a table with records
	being rows, see "{key field}").  Alternatively the key may be
	obtained by applying some function, e.g. a {hash function}, to
	one or more of the fields.  The set of keys for all records
	forms an {index}.  Multiple indexes may be built for one
	database depending on how it is to be searched.

	2. <cryptography> A value which must be fed into the
	{algorithm} used to decode an encrypted message in order to
	reproduce the original {plain text}.  Some encryption schemes
	use the same (secret) key to encrypt and decrypt a message,
	but {public key encryption} uses a "private" (secret) key and
	a "public" key which is known by all parties.

	3. <hardware> An electromechanical {keyboard} button.

	(2003-07-04)

s | key |
key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
| 1 | SIMPLE | case_sessions | ALL | start_date_index | NULL |
NULL | NULL | 423608 | Using where |
+----+-------------+---------------+------+------------------+------
+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00';
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
| 1 | SIMPLE | case_sessions | ref | start_date_index |
start_date_index | 8 | const | 1 | |
+----+-------------+---------------+------+------------------
+------------------+---------+-------+------+-------+
1 row in set (0.00 sec)

Can anyone give me a clue?
Thanks,
RobCREATE TABLE `case_sessions` (
`case_id` bigint(20) NOT NULL default '0',
`start_date` datetime NOT NULL default '0000-00-00 00:00:00',
`end_date` datetime default NULL,
`beginning_status_code` tinytext NOT NULL,
`working_agent_login_id` tinytext,
`updating_system` tinytext,
`updating_agent_login_id` tinytext,
`updating_external_party_id` tinytext,
`creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` varchar(8) NOT NULL default '',
`last_updated_date` datetime NOT NULL default '0000-00-00 00:00:00',
`last_updated_by` varchar(8) NOT NULL default '',
`source` tinytext NOT NULL,
PRIMARY KEY
(`case_id`,`start_date`,`beginning_status_code`(2),`source`(2)),
KEY `start_date_index` USING BTREE (`start_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

From: spamtheaussie

Date: Friday, October 19, 2007

Hopefully this is slightly easier to read:

mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ALL
possible_keys: start_date_index
key: NULL
key_len: NULL
ref: NULL
rows: 412863
Extra: Using where

mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ref
possible_keys: start_date_index
key: start_date_index
key_len: 8
ref: const
rows: 1
Extra:

From: Luuk

Date: Friday, October 19, 2007

<spamtheaussie@xxxxxxxxxxx> schreef in bericht

Hopefully this is slightly easier to read:
mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ALL
possible_keys: start_date_index
key: NULL
key_len: NULL
ref: NULL
rows: 412863
Extra: Using where
mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ref
possible_keys: start_date_index
key: start_date_index
key_len: 8
ref: const
rows: 1
Extra:

using an index, or not, has somethings to do with optimizations...
I have a table wit a datetime field

<data, database> An area of a {database} {record}, or
	{graphical user interface} {form}, into which a particular
	item of data is entered.

	Example usage: "The telephone number field is not really a
	numerical field", "Why do we need a four-digit field for the
	year?".

	A {database} {column} is the set of all instances of a given
	field from all records in a {table}.

	(1999-04-26)

in it. the table contains data

<data, data processing, jargon> /day't*/ (Or "raw data")
	Numbers, {characters}, {images}, or other method of recording,
	in a form which can be assessed by a human or (especially)
	input into a {computer}, stored and {processed} there, or
	transmitted on some {digital channel}.  Computers nearly
	always represent data in {binary}.

	Data on its own has no meaning, only when interpreted by some
	kind of {data processing system} does it take on meaning and
	become {information}.

	People or computers can find patterns in data to perceive
	information, and information can be used to enhance
	{knowledge}.  Since knowledge is prerequisite to wisdom, we
	always want more data and information.  But, as modern
	societies verge on {information overload}, we especially need
	better ways to find patterns.

	1234567.89 is data.

	"Your bank balance has jumped 8087% to $1234567.89" is
	information.

	"Nobody owes me that much money" is knowledge.

	"I'd better talk to the bank before I spend it, because of
	what has happened to other people" is wisdom.

	(1999-04-30)

for every
day since july 2007;

when I do "select * from table where dateTime < '2007-10-19';" the index
with this field is used.

and when I do "select * from table where dateTime >'2007-10-19'; the index
isn't used.

but, last but not least, there's a chapter on this subject

<programming> In {subject-oriented programming}, a subject is
	a collection of {classes} or class fragments whose {class
	hierarchy} models its domain in its own, subjective way.  A
	subject may be a complete application in itself, or it may be
	an incomplete fragment that must be composed with other
	subjects to produce a complete application.  Subject
	composition combines class hierarchies to produce new subjects
	that incorporate functionality from existing subjects.

	(1999-08-31)

in the manual
(http://dev.mysql.com/doc/refman/5.0/en/optimization.html) which i haven't
read......

From: ZeldorBlat

Date: Friday, October 19, 2007

wrote in message:

Hopefully this is slightly easier to read:
mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ALL
possible_keys: start_date_index
key: NULL
key_len: NULL
ref: NULL
rows: 412863
Extra: Using where
mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ref
possible_keys: start_date_index
key: start_date_index
key_len: 8
ref: const
rows: 1
Extra:

What does your data look like? If most of the rows are less than
2007-06-02 08:00 (which appears to be the case) it's probably faster
to scan the table than use an index (fewer disk

<storage> 1. {magnetic disk}.

	2. {compact disc}.

	3. {optical disk}.

	Note: the american spelling, "disk", is normal for most
	computer disks whereas "compact disc", having come to
	computers via the audio world, is correctly spelled with a
	"c", indeed, this spelling is part of the CD standard.

	(1995-07-30)

seeks). The optimizer
is smarter than you think...

From: Luuk

Date: Friday, October 19, 2007

"Luuk" <luuk@xxxxxxxxxxx> schreef in bericht

<spamtheaussie@xxxxxxxxxxx> schreef in bericht

Hopefully this is slightly easier to read:

mysql> explain SELECT * FROM case_sessions where start_date <
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ALL
possible_keys: start_date_index
key: NULL
key_len: NULL
ref: NULL
rows: 412863
Extra: Using where

mysql> explain SELECT * FROM case_sessions where start_date =
'2007-06-02 08:00'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: case_sessions
type: ref
possible_keys: start_date_index
key: start_date_index
key_len: 8
ref: const
rows: 1
Extra:

using an index, or not, has somethings to do with optimizations... I have a table wit a datetime field in it. the table contains data for
every day since july 2007;
when I do "select * from table where dateTime < '2007-10-19';" the index
with this field is used.
and when I do "select * from table where dateTime >'2007-10-19'; the index
isn't used.
but, last but not least, there's a chapter on this subject in the manual
(http://dev.mysql.com/doc/refman/5.0/en/optimization.html) which I have
not read......

hmmmm, is was missing something, so the above isn't true,
damn, I should have read the pages the link

1. <file system> {hard link} or {symbolic link}.

	2. <hypertext> {hyperlink}.

	(1997-10-22)

refers too...

the ebove should be:
- when I do "select * from table where dateTime < '2007-10-19';" the index
with this field isn't used. (but the PRIMARY key is used)
- and when I do "select * from table where dateTime >'2007-10-19'; the index
IS used.

From: Dirk McCormick

Date: Friday, October 19, 2007

1. <data, graphics> Data representing a two-dimensional scene.
	A digital image is composed of {pixels} arranged in a
	rectangular array with a certain height and width.  Each pixel
	may consist of one or more {bits} of information, representing
	the brightness of the image at that point and possibly
	including colour information encoded as {RGB} triples.

	{Images} are usually taken from the real world via a {digital
	camera}, {frame grabber}, or {scanner}; or they may be
	generated by computer, e.g. by {ray tracing} software.

	See also {image formats}, {image processing}.

	(1994-10-21)

	2. <mathematics> The image (or range) of a {function} is the
	set of values obtained by applying the function to all
	elements of its {domain}.  So, if f : D -> C then the set f(D)
	= \{ f(d) | d in D \} is the image of D under f.  The image is
	a subset of C, the {codomain}.

	(2000-01-19)

of start_date is from 2002-07-02 to the present, with
gradually increasing density from then until the present.
It stops using the index when the date is greater than 2006-04-01.
So it makes sense that if it's retrieving all the row data anyway it
may as well just scan the table.

Unfortunately my actual use case is this:
SELECT * FROM case_sessions where start_date < '2006-04-01' and
end_date > '2006-04-01'
I created an end_date_index and a combined index on start_date and
end_date called date_index, but in this case I guess it does not
realise that there are only going to be a handful of rows that will
match the WHERE criteria, so it still tries to
a) use the start_date index instead of using the composite

date_index
b) ignore all indexes after '2006-04-01'

I guess I will just force it to use my composite index.

Thanks a lot for your help!
Rob

From: spamtheaussie

Date: Friday, October 19, 2007

wrote in message:
wrote in message:
> Hopefully this is slightly easier to read:
> mysql> explain SELECT * FROM case_sessions where start_date <
> '2007-06-02 08:00'\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: case_sessions
> type: ALL
> possible_keys: start_date_index
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 412863
> Extra: Using where
> mysql> explain SELECT * FROM case_sessions where start_date =
> '2007-06-02 08:00'\G;
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: case_sessions
> type: ref
> possible_keys: start_date_index
> key: start_date_index
> key_len: 8
> ref: const
> rows: 1
> Extra:
What does your data look like? If most of the rows are less than
2007-06-02 08:00 (which appears to be the case) it's probably faster
to scan the table than use an index (fewer disk seeks). The optimizer
is smarter than you think...

You're right it depends on the date. Interesting.
The range of start_date is from 2002-07-02 to the present, with
gradually increasing density from then until the present.
It stops using the index when the date is greater than 2006-04-01.
So it makes sense that if it's retrieving all the row data anyway it
may as well just scan the table.

Unfortunately my actual use case is this:
SELECT * FROM case_sessions where start_date < '2006-04-01' and
end_date > '2006-04-01'
I created an end_date_index and a combined index on start_date and
end_date called date_index, but in this case I guess it does not
realise that there are only going to be a handful of rows that will
match the WHERE criteria, so it still tries to
a) use the start_date index instead of using the composite date_index
b) ignore all indexes after '2006-04-01'

I guess I will just force it to use my composite index.

Thanks a lot for your help!
Rob

From: Dirk McCormick

Date: Saturday, October 20, 2007

Next Message: restore one database from full backup

Blogs related to Indexes with [code]< or >[/code] comparisons

database status loading
... excel database vbrun300.dll database vbscript database vcard database vds database vegas map zip code list database vehicle options free online database vendor database vendor comparison database vendor market share database vendors ...

wordperfect calendars
5.1 codes wordperfect variables wordperfect version wordperfect version 10 wordperfect version 11 wordperfect version 12 wordperfect version 9 wordperfect version history wordperfect versions wordperfect viewer wordperfect viewer corel ...

cms asp
... hjemmesidewebdesign.dk cms cms hjemmeside hjemmesidewebdesign.dk cms cnc router for sale cms cnc routers cms cnc wood router for sale cms coast gold site web cms code cms code cpt medicare cms code life safety training cms codes cms ...

php mysql client api version
... php mysql club membership database php mysql cms php mysql cms open source php mysql cms tutorial php mysql code php mysql code examples php mysql code generator php mysql code sample php mysql code samples php mysql codes php mysql ...

sql between date
... password sql changing column sql char sql char function sql char functions sql char varchar sql character sql character code sql character codes sql character count sql character escape sql character function sql character functions ...

Area Code Listings
A full listing of all area codes in North America sorted by State is available at: http://www.areacodelookup-usa.com/area-code-listings-by-state.html And the complete index of area codes . Area Code Listings livonia area code li yi ...