***BOGO*** Re: optimize

["Alex Arul Lurthu" <alex.lurthu@xxxxxxxxx>]

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