Do this to prevent duplication on those three columns in the future:
ALTER TABLE url_visit DROP KEY `url_scheme_ID`, add UNIQUE KEY
(url_scheme_ID, url_server_ID, url_path_id);
The way I have composed that key (table-column order), it will force you to
include the url_scheme_ID if you want to use the index to find
url_server_id and url_path_id. Maybe a better Idea is to organize that new
key so that the columns are listed in their order of prevalence in your
queries, if you search by url_sever_ID most often, list it first. If
url_scheme_ID is not something you "need" as often put it last. That
changes the statement to look like:
ALTER TABLE url_visit DROP KEY `url_scheme_ID`, add UNIQUE KEY
(url_server_ID, url_path_ID, url_scheme_ID);
This way you can search on {url_server_ID}, {url_server_ID, url_path_ID},
or (url_server_ID, url_path_ID, url_scheme_ID} and MySQL will still use the
index. Because it's designated as UNIQUE key, there will always be at most
1 record with any combination of those three values.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"J S"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
com> cc: [EMAIL PROTECTED]
Fax to:
06/23/2004 10:38 Subject: Re: load data into 2 tables
and set id
AM
Sorry! I'm not that experienced with databases as you probably realised!
url_visit | CREATE TABLE `url_visit` (
`urlid` mediumint(9) NOT NULL auto_increment,
`url_scheme_ID` int(11) NOT NULL default '0',
`url_server_ID` int(11) NOT NULL default '0',
`url_path_ID` int(11) NOT NULL default '0',
`query` text,
`category` varchar(50) default NULL,
PRIMARY KEY (`urlid`),
KEY `url_scheme_ID` (`url_scheme_ID`,`url_server_ID`,`url_path_ID`)
) TYPE=MyISAM
>
>I can't tell from a DESCRIBE output. Please post the results of SHOW
>CREATE TABLE url_visit;
>
>Thanks!
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
>
> "J S"
> <[EMAIL PROTECTED] To:
[EMAIL PROTECTED]
> com> cc:
>[EMAIL PROTECTED]
> Fax to:
> 06/23/2004 09:57 Subject: Re: load data
>into 2 tables and set id
> AM
>
>
>
>
>
>
>Hi Shawn,
>
>Here's the url_Schemes table (it's the same as the url_paths and
>url_servers). This means url_scheme_ID is part of a unique constraint/key
?
>
>mysql> desc url_schemes;
>+--------+-------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+--------+-------------+------+-----+---------+----------------+
>| ID | int(11) | | MUL | NULL | auto_increment |
>| scheme | varchar(20) | | PRI | | |
>+--------+-------------+------+-----+---------+----------------+
>2 rows in set (0.00 sec)
>
>mysql> desc url_visit;
>+---------------+--------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+---------------+--------------+------+-----+---------+----------------+
>| urlid | mediumint(9) | | PRI | NULL | auto_increment |
>| url_scheme_ID | int(11) | | MUL | 0 | |
>| url_server_ID | int(11) | | | 0 | |
>| url_path_ID | int(11) | | | 0 | |
>| query | text | YES | | NULL | |
>| category | varchar(50) | YES | | NULL | |
>+---------------+--------------+------+-----+---------+----------------+
>6 rows in set (0.00 sec)
>
>mysql> select * from url_schemes;
>+----+--------+
>| ID | scheme |
>+----+--------+
>| 1 | http |
>| 2 | tcp |
>| 3 | - |
>| 4 | ftp |
>| 5 | https |
>+----+--------+
>5 rows in set (0.00 sec)
>
> >
>------ previous responses clipped for space ---------
>
>
>
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
