MySQL Forums
Forum List  »  Optimizer & Parser

does my index take effect?
Posted by: zhenxing zhai
Date: December 21, 2005 03:19AM

I have a table weblogentry,define and index status as follows:

mysql> desc weblogentry;
+---------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------------------+----------------+
| ANCHOR | varchar(255) | NO | | | |
| TITLE | varchar(255) | NO | MUL | | |
| pubtime | timestamp | YES | | CURRENT_TIMESTAMP | |
| updatetime | timestamp | YES | | 0000-00-00 00:00:00 | |
| PUBLISHENTRY | tinyint(4) | NO | MUL | 0 | |
| ALLOWCOMMENTS | tinyint(4) | NO | MUL | 0 | |
| EXTRATEXT2 | varchar(255) | YES | | NULL | |
| KEYWORDS | varchar(255) | YES | | NULL | |
| ISPASS | tinyint(4) | YES | MUL | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| WEBSITEID | int(11) | YES | MUL | NULL | |
| CATEGORYID | int(11) | YES | MUL | NULL | |
| TEXT | text | YES | | NULL | |
| EXTRATEXT | text | YES | | NULL | |
| userid | int(11) | YES | MUL | 0 | |
+---------------+--------------+------+-----+---------------------+----------------+
15 rows in set (0.05 sec)


mysql> show index from weblogentry;
+-------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| weblogentry | 0 | PRIMARY | 1 | id | A | 1295377 | NULL | NULL | | BTREE | |
| weblogentry | 1 | idx_weblogentry_title | 1 | TITLE | A | 647688 | NULL | NULL | | BTREE | |
| weblogentry | 1 | idx_weblogentry_publishentry | 1 | PUBLISHENTRY | A | 11 | NULL | NULL | | BTREE | |
| weblogentry | 1 | idx_weblogentry_allowcomments | 1 | ALLOWCOMMENTS | A | 2 | NULL | NULL | | BTREE | |
| weblogentry | 1 | idx_weblogentry_ispass | 1 | ISPASS | A | 3 | NULL | NULL | YES | BTREE | |
| weblogentry | 1 | idx_weblogentry_websiteid | 1 | WEBSITEID | A | 259075 | NULL | NULL | YES | BTREE | |
| weblogentry | 1 | idx_weblogentry_categoryid | 1 | CATEGORYID | A | 323844 | NULL | NULL | YES | BTREE | |
| weblogentry | 1 | idx_weblogentry_userid | 1 | userid | A | 259075 | NULL | NULL | YES | BTREE | |
| weblogentry | 1 | idx_weblogentry_pub_ispass_id | 1 | PUBLISHENTRY | A | 11 | NULL | NULL | | BTREE | |
| weblogentry | 1 | idx_weblogentry_pub_ispass_id | 2 | ISPASS | A | 24 | NULL | NULL | YES | BTREE | |
| weblogentry | 1 | idx_weblogentry_pub_ispass_id | 3 | id | A | 1295377 | NULL | NULL | | BTREE | |
+-------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.00 sec)

next,I create another table weblogentry_new ,create procedure as follows:

1、show create table weblogentry

2 copy create table statment and execute,

CREATE TABLE `weblogentry_new` (
`ANCHOR` varchar(255) NOT NULL default '',
`TITLE` varchar(255) NOT NULL default '',
`pubtime` timestamp NOT NULL default CURRENT_TIMESTAMP,
`updatetime` timestamp NOT NULL default '0000-00-00 00:00:00',
`PUBLISHENTRY` tinyint(4) NOT NULL default '0',
`ALLOWCOMMENTS` tinyint(4) NOT NULL default '0',
`EXTRATEXT2` varchar(255) default NULL,
`KEYWORDS` varchar(255) default NULL,
`ISPASS` tinyint(4) default NULL,
`id` int(11) NOT NULL auto_increment,
`WEBSITEID` int(11) default NULL,
`CATEGORYID` int(11) default NULL,
`TEXT` text,
`EXTRATEXT` text,
`userid` int(11) default '0',
PRIMARY KEY (`id`),
KEY `idx_weblogentry_title` (`TITLE`),
KEY `idx_weblogentry_publishentry` (`PUBLISHENTRY`),
KEY `idx_weblogentry_allowcomments` (`ALLOWCOMMENTS`),
KEY `idx_weblogentry_ispass` (`ISPASS`),
KEY `idx_weblogentry_websiteid` (`WEBSITEID`),
KEY `idx_weblogentry_categoryid` (`CATEGORYID`),
KEY `idx_weblogentry_userid` (`userid`),
KEY `idx_weblogentry_pub_ispass_id` (`PUBLISHENTRY`,`ISPASS`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk

3 insert into weblogentry_new select * from weblogentry;

4 show index from weblogentry_new

mysql> show index from weblogentry_new;
+-----------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| weblogentry_new | 0 | PRIMARY | 1 | id | A | 1295377 | NULL | NULL | | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_title | 1 | TITLE | A | NULL | NULL | NULL | | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_publishentry | 1 | PUBLISHENTRY | A | NULL | NULL | NULL | | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_allowcomments | 1 | ALLOWCOMMENTS | A | NULL | NULL | NULL | | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_ispass | 1 | ISPASS | A | NULL | NULL | NULL | YES | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_websiteid | 1 | WEBSITEID | A | NULL | NULL | NULL | YES | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_categoryid | 1 | CATEGORYID | A | NULL | NULL | NULL | YES | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_userid | 1 | userid | A | NULL | NULL | NULL | YES | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_pub_ispass_id | 1 | PUBLISHENTRY | A | NULL | NULL | NULL | | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_pub_ispass_id | 2 | ISPASS | A | NULL | NULL | NULL | YES | BTREE | |
| weblogentry_new | 1 | idx_weblogentry_pub_ispass_id | 3 | id | A | NULL | NULL | NULL | | BTREE | |
+-----------------+------------+-------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

column Cardinality of all indexs is null expect primary key ,I don't know if these indexes have been updated? if I need create these indexes repead?if haven't been updated,why only primary key has been updated?

Options: ReplyQuote


Subject
Views
Written By
Posted
does my index take effect?
3304
December 21, 2005 03:19AM
2073
December 21, 2005 11:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.