Re: Wrong estimation of Rows in explain

[Rathi Rao]

Hi Peter,

Please find the create table details and explain plan details

PS : I could not attach the plan details, so formatted as much as I could

CREATE TABLE activityinformation (
source varchar(255) ,
ActivityId int(11) ,
ActivityName varchar(200) ,
ActivityPlanId int(11) ,
ActivityPlanName varchar(200),
CropTypeId int(11) ,
ActivityScheduleId int(11),
ActivityIntervalDays int(11) ,
AttributeDataTypeId int(11) ,
SequenaceNo int(11) ,
AttributeId int(11) ,
AttributeName varchar(100) ,
DAS int(11) ,
DataTypeID int(11) ,
DataTypeName varchar(100),
varietyid int(11) ,
farmercropid int(11) ,
talendate datetime ,
companyid int(11) ,
isactive int(11)
) PARTITION BY LIST (isactive)
(PARTITION pactive VALUES IN (1) ,
PARTITION pinactive VALUES IN (0) ) ;

CREATE TABLE FarmerCropActivity (
FarmerCropActivityID int(11),
FarmerCropId int(11) ,
ActivityScheduleID int(11) ,
CompletionDate datetime,
ExpectedStartDate datetime,
ExpectedClosureDate datetime ,
Suggestions varchar(5000) ,
Comments varchar(5000) ,
IsClosed bit(1) ,
IsActive bit(1) ,
LastModifiedDate datetime ,
LastModifiedBy int(11) ,
ActivityID int(11) ,
ActivityReading varchar(100) ,
IsMandatory bit(1),
ActivityCategoryID int(11) ,
ClientId varchar(5000)
);

EXPLAIN PLAN -

1 SIMPLE ai ALL idx_ai_activityid,idx_ai_farmercropid 3161049 100.00 Using where

1 SIMPLE fca ref idx_fca_activityid,idx_fca_farmercropid idx_fca_farmercropid 4 devwarehouse.ai.farmercropid 17 100.00 Using where


Thanks
Rathi