different type column and keys for EXPLAIN

------=_NextPart_000_0098_01CA7CE3.96C47BC0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.



Please refer to the below two statements. First query is checking for
lastname 'clarke' where as second query is checking for lastname 'clark'.
Rest everything is same with these two queries. However, the explain output
shows "ref" for the first query and uses only one key for the first query
whereas second query uses "index_merge" and both keys.



mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' and
fldLASTNAME='clarke';

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

| 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME
| 31 | const | 35043 | Using where |

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

1 row in set (0.07 sec)



mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' and
fldLASTNAME='clark';

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

| 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME |
fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using
intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index |

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

1 row in set (0.02 sec)



What could be the problem here. Please help.



Thanks,

Manish


------=_NextPart_000_0098_01CA7CE3.96C47BC0--

Re: different type column and keys for EXPLAIN

Manish,

On Mon, Dec 14, 2009 at 05:33:43PM +0530, Manish Ranjan wrote:
> I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.
>
>
>
> Please refer to the below two statements. First query is checking for
> lastname 'clarke' where as second query is checking for lastname 'clark'.
> Rest everything is same with these two queries. However, the explain output
> shows "ref" for the first query and uses only one key for the first query
> whereas second query uses "index_merge" and both keys.
>
> ...
>
> What could be the problem here. Please help.

"ref" and "index_merge" are two possible plans for both of the queries. The
choice whether to use ref or index_merge depends on cost calculations, which,
in turn, depends on estimates of numbers of records that one will get for
conditions in the WHERE clause.

It seems that the storage engine reports different estimates for number of
matching records for lastname='clark' and lastname='clarke', and hence the
query plans are different. This is a normal situation.

Does that cause any problems for you? That is, do you observe that one of the
queries is unccecessarily slow (i.e. much slower than you could make it to run
by using some hint?)

BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: different type column and keys for EXPLAIN

--000e0cd56c3206cb8b047ab190e0
Content-Type: text/plain; charset=ISO-8859-1

I don't think there's an actual problem as such, the optimiser is just
making a decision to merge the lastname and firstname indices for the second
query. At a guess, I'd say that the cardinality of "clark" in your lastname
index is too high, so it uses both; the cardinality for "clarke" will be
lower, and probably low enough that using just the one index becomes faster.

No guarantees, though - that's just what it looks like from here.

On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan
wrote:

> I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.
>
>
>
> Please refer to the below two statements. First query is checking for
> lastname 'clarke' where as second query is checking for lastname 'clark'.
> Rest everything is same with these two queries. However, the explain output
> shows "ref" for the first query and uses only one key for the first query
> whereas second query uses "index_merge" and both keys.
>
>
>
> mysql> explain select count(*) from tblList where fldFIRSTNAME='michael'
> and
> fldLASTNAME='clarke';
>
>
> +----+-------------+-------------------+------+------------- -------------+--
> -----------+---------+-------+-------+-------------+
>
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows | Extra |
>
>
> +----+-------------+-------------------+------+------------- -------------+--
> -----------+---------+-------+-------+-------------+
>
> | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME |
> fldLASTNAME
> | 31 | const | 35043 | Using where |
>
>
> +----+-------------+-------------------+------+------------- -------------+--
> -----------+---------+-------+-------+-------------+
>
> 1 row in set (0.07 sec)
>
>
>
> mysql> explain select count(*) from tblList where fldFIRSTNAME='michael'
> and
> fldLASTNAME='clark';
>
>
> +----+-------------+-------------------+-------------+------ ----------------
>
> ----+--------------------------+---------+------+------+---- ----------------
> -------------------------------------------------+
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows | Extra
> |
>
>
> +----+-------------+-------------------+-------------+------ ----------------
>
> ----+--------------------------+---------+------+------+---- ----------------
> -------------------------------------------------+
>
> | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME |
> fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using
> intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index |
>
>
> +----+-------------+-------------------+-------------+------ ----------------
>
> ----+--------------------------+---------+------+------+---- ----------------
> -------------------------------------------------+
>
> 1 row in set (0.02 sec)
>
>
>
> What could be the problem here. Please help.
>
>
>
> Thanks,
>
> Manish
>
>

--000e0cd56c3206cb8b047ab190e0--

RE: different type column and keys for EXPLAIN

------=_NextPart_000_00D6_01CA7D00.86000260
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Thanks Johan.

More to add here, execution time for query with 'clarke' is much higher than
query with 'clark'. This is why it looks a bit strange behavior.



From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
Meersman
Sent: Monday, December 14, 2009 8:33 PM
To: Manish Ranjan
Cc: mysql@lists.mysql.com
Subject: Re: different type column and keys for EXPLAIN



I don't think there's an actual problem as such, the optimiser is just
making a decision to merge the lastname and firstname indices for the second
query. At a guess, I'd say that the cardinality of "clark" in your lastname
index is too high, so it uses both; the cardinality for "clarke" will be
lower, and probably low enough that using just the one index becomes faster.

No guarantees, though - that's just what it looks like from here.

On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan
wrote:

I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.



Please refer to the below two statements. First query is checking for
lastname 'clarke' where as second query is checking for lastname 'clark'.
Rest everything is same with these two queries. However, the explain output
shows "ref" for the first query and uses only one key for the first query
whereas second query uses "index_merge" and both keys.



mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' and
fldLASTNAME='clarke';

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

| 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME
| 31 | const | 35043 | Using where |

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

1 row in set (0.07 sec)



mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' and
fldLASTNAME='clark';

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

| 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME |
fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using
intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index |

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

1 row in set (0.02 sec)



What could be the problem here. Please help.



Thanks,

Manish




------=_NextPart_000_00D6_01CA7D00.86000260--

Re: different type column and keys for EXPLAIN

--0015176f115c48a4dd047ab224cd
Content-Type: text/plain; charset=ISO-8859-1

Heh. Try running "analyze table", so the index stats are correct. If that
doesn't help, you may have stumbled upon an optimizer glitch, or maybe
there's something happening that I'm not seeing. If all else fails, try to
add index hints.

On Mon, Dec 14, 2009 at 4:30 PM, Manish Ranjan
wrote:

> Thanks Johan.
>
> More to add here, execution time for query with 'clarke' is much higher
> than
> query with 'clark'. This is why it looks a bit strange behavior.
>
>
>
> From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
> Meersman
> Sent: Monday, December 14, 2009 8:33 PM
> To: Manish Ranjan
> Cc: mysql@lists.mysql.com
> Subject: Re: different type column and keys for EXPLAIN
>
>
>
> I don't think there's an actual problem as such, the optimiser is just
> making a decision to merge the lastname and firstname indices for the
> second
> query. At a guess, I'd say that the cardinality of "clark" in your lastname
> index is too high, so it uses both; the cardinality for "clarke" will be
> lower, and probably low enough that using just the one index becomes
> faster.
>
> No guarantees, though - that's just what it looks like from here.
>
> On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan <
> manish.ranjan@stigasoft.com>
> wrote:
>
> I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.
>
>
>
> Please refer to the below two statements. First query is checking for
> lastname 'clarke' where as second query is checking for lastname 'clark'.
> Rest everything is same with these two queries. However, the explain output
> shows "ref" for the first query and uses only one key for the first query
> whereas second query uses "index_merge" and both keys.
>
>
>
> mysql> explain select count(*) from tblList where fldFIRSTNAME='michael'
> and
> fldLASTNAME='clarke';
>
>
> +----+-------------+-------------------+------+------------- -------------+--
> -----------+---------+-------+-------+-------------+
>
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows | Extra |
>
>
> +----+-------------+-------------------+------+------------- -------------+--
> -----------+---------+-------+-------+-------------+
>
> | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME |
> fldLASTNAME
> | 31 | const | 35043 | Using where |
>
>
> +----+-------------+-------------------+------+------------- -------------+--
> -----------+---------+-------+-------+-------------+
>
> 1 row in set (0.07 sec)
>
>
>
> mysql> explain select count(*) from tblList where fldFIRSTNAME='michael'
> and
> fldLASTNAME='clark';
>
>
> +----+-------------+-------------------+-------------+------ ----------------
>
> ----+--------------------------+---------+------+------+---- ----------------
> -------------------------------------------------+
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows | Extra
> |
>
>
> +----+-------------+-------------------+-------------+------ ----------------
>
> ----+--------------------------+---------+------+------+---- ----------------
> -------------------------------------------------+
>
> | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME |
> fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using
> intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index |
>
>
> +----+-------------+-------------------+-------------+------ ----------------
>
> ----+--------------------------+---------+------+------+---- ----------------
> -------------------------------------------------+
>
> 1 row in set (0.02 sec)
>
>
>
> What could be the problem here. Please help.
>
>
>
> Thanks,
>
> Manish
>
>
>
>

--0015176f115c48a4dd047ab224cd--

RE: different type column and keys for EXPLAIN

------=_NextPart_000_00DE_01CA7D03.559B3790
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Already did "Analyze table" and table is up to date. It seems like optimizer
glitch only but I am not sure. Will try adding index hints.





From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
Meersman
Sent: Monday, December 14, 2009 9:14 PM
To: Manish Ranjan
Cc: mysql@lists.mysql.com
Subject: Re: different type column and keys for EXPLAIN



Heh. Try running "analyze table", so the index stats are correct. If that
doesn't help, you may have stumbled upon an optimizer glitch, or maybe
there's something happening that I'm not seeing. If all else fails, try to
add index hints.

On Mon, Dec 14, 2009 at 4:30 PM, Manish Ranjan
wrote:

Thanks Johan.

More to add here, execution time for query with 'clarke' is much higher than
query with 'clark'. This is why it looks a bit strange behavior.



From: vegivamp@gmail.com [mailto:vegivamp@gmail.com] On Behalf Of Johan De
Meersman
Sent: Monday, December 14, 2009 8:33 PM
To: Manish Ranjan
Cc: mysql@lists.mysql.com
Subject: Re: different type column and keys for EXPLAIN




I don't think there's an actual problem as such, the optimiser is just
making a decision to merge the lastname and firstname indices for the second
query. At a guess, I'd say that the cardinality of "clark" in your lastname
index is too high, so it uses both; the cardinality for "clarke" will be
lower, and probably low enough that using just the one index becomes faster.

No guarantees, though - that's just what it looks like from here.

On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan
wrote:

I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.



Please refer to the below two statements. First query is checking for
lastname 'clarke' where as second query is checking for lastname 'clark'.
Rest everything is same with these two queries. However, the explain output
shows "ref" for the first query and uses only one key for the first query
whereas second query uses "index_merge" and both keys.



mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' and
fldLASTNAME='clarke';

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

| 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | fldLASTNAME
| 31 | const | 35043 | Using where |

+----+-------------+-------------------+------+------------- -------------+--
-----------+---------+-------+-------+-------------+

1 row in set (0.07 sec)



mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' and
fldLASTNAME='clark';

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

| 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME |
fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using
intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index |

+----+-------------+-------------------+-------------+------ ----------------
----+--------------------------+---------+------+------+---- ----------------
-------------------------------------------------+

1 row in set (0.02 sec)



What could be the problem here. Please help.



Thanks,

Manish







------=_NextPart_000_00DE_01CA7D03.559B3790--

RE: different type column and keys for EXPLAIN

Thanks Sergey.

The query is much slower with "ref". Do you think if a composite index on
firstname and lastname would solve it? Table has 164+ million records which
makes me reluctant to create a new index due to the time required for index
creation unless I am pretty sure that the new index would work.


-----Original Message-----
From: Sergey Petrunya [mailto:psergey@askmonty.org]
Sent: Monday, December 14, 2009 5:58 PM
To: Manish Ranjan
Cc: mysql@lists.mysql.com
Subject: Re: different type column and keys for EXPLAIN

Manish,

On Mon, Dec 14, 2009 at 05:33:43PM +0530, Manish Ranjan wrote:
> I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM.
>
>
>
> Please refer to the below two statements. First query is checking for
> lastname 'clarke' where as second query is checking for lastname 'clark'.
> Rest everything is same with these two queries. However, the explain
output
> shows "ref" for the first query and uses only one key for the first query
> whereas second query uses "index_merge" and both keys.
>
> ...
>
> What could be the problem here. Please help.

"ref" and "index_merge" are two possible plans for both of the queries. The
choice whether to use ref or index_merge depends on cost calculations,
which,
in turn, depends on estimates of numbers of records that one will get for
conditions in the WHERE clause.

It seems that the storage engine reports different estimates for number of
matching records for lastname='clark' and lastname='clarke', and hence the
query plans are different. This is a normal situation.

Does that cause any problems for you? That is, do you observe that one of
the
queries is unccecessarily slow (i.e. much slower than you could make it to
run
by using some hint?)

BR
Sergey
--
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org