***BOGO*** Re: optimize

["Ananda Kumar" <anandkl@xxxxxxxxx>]

Krishna,
Since the number matching rows for the giving where CONDITION is accouting
to this many rows, the query is reading that much data.

please try this

select count(*) from user_info ui,user_authentication_date
uad where ui.user_id!=uad.user_id;

The above query will tell u how many records will be read for THE WHERE
CONDITION UR TRYING.

regards
anandkl


On 5/28/08, Krishna Chandra Prajapati <prajapatikc@xxxxxxxxx> wrote:
>
> I would like to know why it is scanning 255629,267072 rows, Since user_id
> in
> both the table is a primary key (indexed). Please tell me how can i
> optimize
> this.
>
>
> On Wed, May 28, 2008 at 12:59 PM, Alex Arul Lurthu <alex.lurthu@xxxxxxxxx>
> wrote:
>
> > Can you please elaborate on what exactly are you trying to achieve by the
> > below mentioned query :
> >
> > mysql> explain select ui.user_id from user_info
> ui,user_authentication_date
> > uad where ui.user_id!=uad.user_id;
> >
> > I have a strange feeling that the join condition is not right.
> >
> >
> > On Wed, May 28, 2008 at 12:51 PM, Krishna Chandra Prajapati <
> > prajapatikc@xxxxxxxxx> wrote:
> >
> >> Hi,
> >>
> >> CREATE TABLE `user_info` (
> >>  `user_id` decimal(22,0) NOT NULL default '0',
> >>  `drivers_license` varchar(24) NOT NULL default '',
> >>  `login_date` datetime NOT NULL default '0000-00-00 00:00:00',
> >>  `course_id` decimal(22,0) NOT NULL default '0',
> >>  `regulator_id` decimal(22,0) NOT NULL default '0',
> >>  `test_info` varchar(16) default NULL,
> >>  `completion_date` datetime default NULL,
> >>  `print_date` datetime default NULL,
> >>  `password` varchar(32) default NULL,
> >>  `certificate_number` varchar(16) default NULL,
> >>  PRIMARY KEY  (`user_id`),
> >>  KEY `idx_certificate_number` (`certificate_number`),
> >>  KEY `idx_completion_date` (`completion_date`),
> >>  KEY `idx_print_date` (`print_date`),
> >>  KEY `idx_user_info` (`drivers_license`),
> >>  KEY `idx_user_info_2` (`course_id`)
> >> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
> >>
> >> CREATE TABLE `user_authentication_date` (
> >>  `user_id` decimal(22,0) NOT NULL default '0',
> >>  `authentication_date` datetime NOT NULL default '0000-00-00 00:00:00',
> >>  PRIMARY KEY  (`user_id`),
> >>  KEY `user_authentication_comp1` (`user_id`,`authentication_date`),
> >>  CONSTRAINT `fk_user_authentication_date` FOREIGN KEY (`user_id`)
> >> REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO
> ACTION
> >> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
> >>
> >> mysql> explain select ui.user_id from user_info
> >> ui,user_authentication_date
> >> uad where ui.user_id=uad.user_id;
> >>
> >>
> +----+-------------+-------+--------+-----------------------------------+---------+---------+-----------------+--------+-------------+
> >> | id | select_type | table | type   | possible_keys
> |
> >> key     | key_len | ref             | rows   | Extra       |
> >>
> >>
> +----+-------------+-------+--------+-----------------------------------+---------+---------+-----------------+--------+-------------+
> >> |  1 | SIMPLE      | uad   | index  | PRIMARY,user_authentication_comp1
> |
> >> PRIMARY | 10      | NULL            | 255629 | Using index |
> >> |  1 | SIMPLE      | ui    | eq_ref | PRIMARY
> |
> >> PRIMARY | 10      | dip.uad.user_id |      1 | Using index |
> >>
> >>
> +----+-------------+-------+--------+-----------------------------------+---------+---------+-----------------+--------+-------------+
> >>
> >> Why it is scanning 255629 rows, Since user_id in both the table is a
> >> primary
> >> key (indexed). Please tell me how can i optimize this.
> >>
> >> mysql> explain select ui.user_id from user_info
> >> ui,user_authentication_date
> >> uad where ui.user_id!=uad.user_id;
> >>
> >>
> +----+-------------+-------+-------+---------------+---------------------+---------+------+--------+--------------------------+
> >> | id | select_type | table | type  | possible_keys | key
> |
> >> key_len | ref  | rows   | Extra                    |
> >>
> >>
> +----+-------------+-------+-------+---------------+---------------------+---------+------+--------+--------------------------+
> >> |  1 | SIMPLE      | uad   | index | NULL          | PRIMARY
> |
> >> 10      | NULL | 255629 | Using index              |
> >> |  1 | SIMPLE      | ui    | index | NULL          | idx_completion_date
> |
> >> 9       | NULL | 267072 | Using where; Using index |
> >>
> >>
> +----+-------------+-------+-------+---------------+---------------------+---------+------+--------+--------------------------+
> >>
> >> --
> >> Krishna Chandra Prajapati
> >> MySQL DBA,
> >>
> >
> >
> >
> > --
> > Thanks
> > Alex
> > http://alexlurthu.wordpress.com
>
>
>
>
> --
> Krishna Chandra Prajapati
> MySQL DBA,
> Ed Ventures e-Learning Pvt.Ltd.
> 1-8-303/48/15, Sindhi Colony
> P.G.Road, Secunderabad.
> Pin Code: 500003
> Office Number: 040-66489771
> Mob: 9912924044
> URL: ed-ventures-online.com
> Email-id: prajapatikc@xxxxxxxxx
>