Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

wwwxxxAPC, How to unsubscrube from dategen spam, WWWXXXAPC, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text

Links

XODOX
Impressum

#1: Simple MySQL Index

Posted on 2007-06-29 05:59:43 by McMurphy

I have a single table which I would 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 don't. 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 is not being used when this column is searched ?

Is this right or am I missing something ?

Thanks in advance...

Report this message

#2: Re: Simple MySQL Index

Posted on 2007-07-05 15:50:34 by lark

McMurphy wrote:
> I have a single table which I would 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 don't. 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 is not being used when this column is searched ?
>
> Is this right or am I missing something ?
>
> Thanks in advance...


looks like your query is for table employees but the explain is run on
properties. these are two different tables!!!

Report this message