List:General Discussion« Previous MessageNext Message »
From:Manish Ranjan Date:December 14 2009 3:50pm
Subject:RE: different type column and keys for EXPLAIN
View as plain text  
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@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De
Meersman
Sent: Monday, December 14, 2009 9:14 PM
To: Manish Ranjan
Cc: mysql@stripped
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 <manish.ranjan@stripped>
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@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De
Meersman
Sent: Monday, December 14, 2009 8:33 PM
To: Manish Ranjan
Cc: mysql@stripped
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@stripped>
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




 



Thread
different type column and keys for EXPLAINManish Ranjan14 Dec
Re: different type column and keys for EXPLAINJohan De Meersman14 Dec
  • RE: different type column and keys for EXPLAINManish Ranjan14 Dec
RE: different type column and keys for EXPLAINManish Ranjan14 Dec
Re: different type column and keys for EXPLAINJohan De Meersman14 Dec
Re: different type column and keys for EXPLAINSergey Petrunya15 Dec
  • RE: different type column and keys for EXPLAINManish Ranjan15 Dec