***BOGO*** Re: optimize

["Krishna Chandra Prajapati" <prajapatikc@xxxxxxxxx>]

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