Simple MySQL Index

2 Message(s) by 2 Author(s) originally posted in mysql database

From: McMurphy

Date: Thursday, June 28, 2007

I have a single table

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

	(1997-06-04)

which I'd like to search on a unique column
varchar(15) that may have some null

<programming> A special value used in several languages to
	represent the thing referred to by an uninitialised pointer.

	<database> A special value that may be stored in some database
	columns to represent an unknown, missing, not applicable, or
	undefined value.  Nulls are treated completely differently
	from ordinary values when evaluating SQL expressions and there
	are several SQL constructs for dealing with nulls.

	(2003-06-17)

s. Employee social club member no,
some employees have a number and others do not. Those that do have a number
will all have a unique number.

I had added an 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)

using:
ALTER TABLE employees ADD INDEX(emp_socialclubno);

However when I run

<operating system, programming> The process of carrying out
	the {instructions} in a computer program by a computer.

	See also {dry run}.

	(1996-05-13)

:
mysql> explain select employeeid from employees where
emp_socialclubno=103833988;
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| 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 | 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 | 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)

From: lark

Date: Thursday, July 05, 2007

wrote in message:

I have a single table which I'd like to search on a unique column
varchar(15) that may have some nulls. Employee social club member no,
some employees have a number and others do not. Those that do have a number
will all have a unique number.
I had added an index using:
ALTER TABLE employees ADD INDEX(emp_socialclubno);
However when I run:
mysql> explain select employeeid from employees where
emp_socialclubno=103833988;
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | properties | ALL | emp_socialclubno | NULL | NULL |
NULL | 170361 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
So this indicates that even though the emp_socialclubno column has an index
it isn't being used when this column is searched ?
Is this right or am I missing something ?
Thanks in advance...looks like your query
1. <database, information science> A user's (or {agent}'s)
	request for information, generally as a formal request to a
	{database} or {search engine}.

	{SQL} is the most common {database query language}.

	2. <character> {question mark}.

	(1997-04-09)
is for table employees but the explain is run on

properties. these are two different tables!!!

Next Message: mysqldump select query

Blogs related to Simple MySQL Index

Re: Simple MySQL problem
Re: Simple MySQL problem.

MySQL-Simple-Query-Browser-0.1.10
I've write a simple script to manage MySQL database, you can see it at http://docs.google.com/Doc?id=dhr2c3jq_8g3vv2t below is from that link, but not valid for write rules #!/usr/bin/env python """ for my lovely parent ...

Re: simple mysql statement giving error.. can't figure it out.
Re: simple mysql statement giving error.. can't figure it out.

Very simple MySql question.. I think.
Very simple MySql question.. I think.

Multiple-column index not working
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE messages ref recipientid recipientid 4 const,const 8352 It seems as though MySQL is not traversing the multiple column index to the status column. ...

Re: -MaxRecords
Jason Huck to Lasso Talk on 2007-07-05 07:35:50 - On 7/5/07, Simon Forster wrote: > Smells a little hack like (why doesn't -maxrecords do this > automagically if talking to MySQL? Ah. Probably because there's no > easy way to get total ...