MYSQL :: UNIQUE And INDEX Key Conflict

Performance Of UNIQUE Index Compared To "normal" INDEX
I have a table with several columns. one column "myColumn" of this table has a UNIQUE index on it. i want to use this column in many many search queries.

does it make sense - in terms of performance - to add an additional INDEX to "myColumn"? or does an UNIQUE index already "include" a "normal" INDEX?

Index & Unique Key
I created a table with two columns 'user_id' and 'name', and defined the combination of the two columns as UNIQUE and defined an INDEX on each column.
Why is the following error generated: UNIQUE and INDEX keys should not both be set for column `user_id`?

Unique Key Also Act As Index
someone said that a unique index also acts as a normal index for the respective fields. Is that true?

I :
- need unique constraint and indexing on a field
- don't need redundant indexing on the field

So I don't need to add a normal index on the which has unique index already applied, is this correct?

UNIQUE Vs INDEX
i have a table with two fields. i want both to be indexed since i will be searching with both of them, and at the same time i want to ensure uniqueness on these two fields together.i cannot create an index on both fields individually and create a unique key on both fields. does creating a unique key on both fields accomplish the same as indexing>?

UNIQUE Index
I got this sql from a MYSQL book, I don't understand why they have email_address there?

CREATE TABLE TEST4 (ID INT,
EMAIL VARCHAR(100),
NAME VARCHAR(40),
ADDRESS VARCHAR(100),
UNIQUE email_address (EMAIL),
PRIMARY KEY (ID));

Unique Index
[ edit by moderator: post split to a new thread, because it is a new topic unrelated to the previous ]

Just out of curiosity, if I create a Unique Index on three fields, does that make each of those fields "indexed" or if those are often searched on should I still index each of them?Also, as far as a primary key: should I always have a primary key on the table? Even if I will never use it in a query? Or is a unique key (that combines several columns) enough?

Should A UNIQUE Column Have An Index?
Would I improve performance if I was to give each column that is expected to
have a unique value, a key/index?

Can I Have Both 'Unique' & 'Index' On A Column?
I have a mysql table and I use wizmysqladmin to make modifications to it.

I have declared 'Index' on a column while setting up the table.

later, I understood that elements in that column are 'unique'.
So, I have declared 'unique' on that column.

Now, that column is having both 'index' and 'unique'

is it ok or shall I remove one of the two?

Should A UNIQUE Column Have An Index?
Would I improve performance if I was to give each column that is expected to
have a unique value, a key/index?

Default Index Is Unique
I've created quite a big schema primarily using query browser and, after begining to enter data and immediately encountering problems, I've discovered, in this order:
1/ Every field in every table has been given an index - weird as I never explicitly used the word index.
2/ Much worse: Every index has unique attribute.
It seems when using simple "create table" syntax, I've somehow created a unique index on every field.
My main question is, what's the quickest way to drop hundreds of unused indexes?
But the question nagging my mind is HOW has MySQL interpreted my intention this way? Does MySQL expext there's only one 'Jack' in the world?

Primary, Index, Unique
I read a tutorial about creating database, and it said I should make the unique field (the id of each record) PRIMARY, INDEX, UNIQUE. However, in phpMyAdmin, the option to select one of these fields is placed in a way so that I can only select one. The options are Pirmary, Index, Unique, ---, and Fulltext.

Field In Primary And Unique Index
I have a lookup table with two fields (username, email).
They both form part of the primary key.

I also created a unique index on username.

The table structure:

CREATE TABLE `user_lookup` (
`username` varchar(30) NOT NULL default '',
`email` varchar(80) NOT NULL default '',
PRIMARY KEY (`username`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I want individual usernames to be unqiue, as well as the username/email combination enforced by the primary key.
When I set the unique index on username phpMyAdmin issues this warning:

Quote:

PRIMARY and INDEX keys should not both be set for column `username`

I just want to know if this is bad practice, and why.
As far as I am concerned I have a valid purpose for both indexes. Is this warning coming from MySQL or is it just something the phpMyAdmin developers have come up with?

Absolute Unique Index Across Two Columns
I am coming across a problem setting up my database. I want to have a table called double matches which holds information about matches between two people. It has w two columns: person1 and person2.

I want to ensure that each row is unique across both columns. However, I need this work no matter which person is entered in which column. For example,

person1 person2
"Joe" "Jane"
"Jane" "Joe"

should be invalid as that is the same "double match." So I would want a query that inserts a duplicate to not insert it. I've played around with having a unique index across two columns, but it doesn't recognize the uniqueness both ways.

I.e. it will allow input of rows which have person1 and person2 switched from an existing row. I've had some trouble identifying the right terms to search, thought I'd ask her. The next step would then be how to search that table for all rows containing one name, ("Joe") and returning a list of his partners ("Jane").

Special Characters In UNIQUE Index
I create the following table:

CREATE TABLE `test` (
`name` varchar(255) default NULL,
UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM CHECKSUM=1 CHARACTER SET latin1;

then

insert into test values ('El Nino')
insert into test values ('El NiÃ±o')

Now I get a duplicate key error.

Who knows reason for this. Is there a solution?

(I need to store filenames in a table)

Unique Index With Nulls Where Is It Stored
The memory space defined by DataMemory is also used to store ordered indexes, which use about 10 bytes per record. Each table row is represented in the ordered index. A common error among users is to assume that all indexes are stored in the memory allocated by IndexMemory, but this is not the case:

Only primary key and unique hash indexes use this memory; ordered indexes use the memory allocated by DataMemory. However, creating a primary key or unique hash index also creates an ordered index on the same keys, unless you specify USING HASH in the index creation statement. This can be verified by running ndb_desc -d db_name table_name in the management client. if i issue a :

create unique index idx_b on tablename(b);

where b can contain nulls is this index stored in index memory and in data memory or just data memory?

Is Unique Better Than Normal Index (in My Case)?
Is search with a Unique index faster than a normal Index (that accepts duplicates)? Here's the actual situation. I have an average mysql table (maximum tens of thousands records - probably will grow in future), with myISAM storage engine. The table will be used mostly with SELECTS. Almost no insterts or UPDATES.
The search will almost always be done like this:

SELECT col3 FROM table WHERE col1=x AND (col2=y OR COL2=const)

col1 = mediumint(6)
col2 = char(32)

I've made an index from col1 and col2, but set the length for col2 to 16 so the index will not be to large.

col1 and col2 form together an unique record so i could make an unique index from the 2 of them - but..the length of the index for col2 must be set to full 32 so there wont be any colisions.

so..is it faster the search with the unique index col1(6)col2(32), or the search with normal index col1(6)col2(16).

MySQL Unique Index Acting Funny?
I have a unique index on one of our tables, this no longer comes up with errors on duplicate trades. I cannot find anything in the errors logs.

CREATE UNIQUE INDEX tradeSeq ON trade_messages (trade_seq);

mysql> INSERT INTO trade_messages SET symbol='E:YGL' ,trade_seq=淙&#56447;' ,trade_size=񠫠' ,trade_price=Ɔ.275' ,ask_price=Ɔ.28' ,bid_price=Ɔ.27' ,date_trade=FROM_UNIXTIME(&#57039;') ,chg=Ɔ.02' ,pct_chg=ƍ.84' ,trade_vol=뷩&#56581;' ,trade_low=Ɔ.26' ,trade_high=Ɔ.285' ,trade_open=Ɔ.26' ,ask_size=泧&#56928;' ,bid_size=&#57149;' ,current_price=Ɔ.275' ,trade_yest_close=Ɔ.255' ,date_quote=FROM_UNIXTIME(&#56380;') ,date_activity=FROM_UNIXTIME(&#56380;') ,date_current=FROM_UNIXTIME(&#56861;.728') ,rawSymbol='E:YGL' ;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO trade_messages SET symbol='E:YGL' ,trade_seq=淙&#56447;' ,trade_size=񠫠' ,trade_price=Ɔ.275' ,ask_price=Ɔ.28' ,bid_price=Ɔ.27' ,date_trade=FROM_UNIXTIME(&#57039;') ,chg=Ɔ.02' ,pct_chg=ƍ.84' ,trade_vol=뷩&#56581;' ,trade_low=Ɔ.26' ,trade_high=Ɔ.285' ,trade_open=Ɔ.26' ,ask_size=泧&#56928;' ,bid_size=&#57149;' ,current_price=Ɔ.275' ,trade_yest_close=Ɔ.255' ,date_quote=FROM_UNIXTIME(&#56380;') ,date_activity=FROM_UNIXTIME(&#56380;') ,date_current=FROM_UNIXTIME(&#56861;.728') ,rawSymbol='E:YGL' ;
Query OK, 1 row affected (0.00 sec)
There are loads of index files.

root@BB Data 1 ~ ls --width=1 /home/mysql/data5/ | grep '-bin' | wc -l
743

Supressing Unique Index Violation Errors
I have the following query in a php/mysql application:

insert tags ( `tag_name`, `tag_entry_id` )
values ( 'cory', 3 ),
( 'bob', 3 ),
( 'tim', 3 )

Unique Index On String Column Containing German Umlaut
Is this a bug or a feature:

I first create this simple table.

CREATE TABLE Test
( ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(10) NOT NULL,

PRIMARY KEY (ID),
UNIQUE INDEX (Name)
);

Then I try to add the following two records:

INSERT INTO Test (Name) VALUES ("Muller");
INSERT INTO Test (Name) VALUES ("MÃ¼ller");

"Muller" and "MÃ¼ller" are not the same,
still I get the error
"Duplicate entry 'MÃ¼ller' for key 2".

I'm using MySQL Server 4.0.18-nt (Windows 2000).

Should I send a bug report?

Version Conflict
I've created a stored proc using MySQL Administrator Ver 1.1.9 which runs fine in MySQL Query Browser 1.1.20. However, when I use it in a C++ program it gives me the following error:

[MySQL][ODBC 3.51 Driver][mysqld-5.0.22-community-nt]You have an error in your SQL syntax; check the manual that corresponds t
your MySQL server version for the right syntax to use near'{0= call 'GetAllJobDetails2'}at line 1.

The first line in the stored proc is:

CREATE DEFINER=`regan`@`localhost` PROCEDURE `GetAllJobDetails2`()

That is what the MySQL Administrator put in when I created it.

Version Conflict
This works perfectly in v4, but in v3.23 it throws a syntax error. I can't see what it is at all...

SELECT DISTINCT page_system_name
FROM page_data JOIN menu_data ON menu_data.menu_group = page_data.menu_group
WHERE page_hidden = 0
ORDER BY menu_data.menu_group_display_order, page_data.page_display_order

SQL LIMIT Conflict
I'm using Dreamweaver to create a list from a MySQL table, however I want to limit the list using the SQL syntax "1,999" (so that I can start at the second record and go from there.)

However when I load the page on the server I get an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 5' at line 1

I'm assuming that this must mean that there is some sort of conflict and that Dreamweaver is setting its own LIMIT on the recordset.

Question is how cna I influence the LIMIT that DMX puts on the recordset? It works fine when I take out my LIMIT clause form the query.

Column Name Conflict
I am trying to use the spatial extensions to mysql. In order to check out the latest I checked out the recommended 5.1.24-rc bk branch and built it. Subsequently I discovered that I can no longer create a table with a column name of range. It worked with the default mysql that comes with Fedora Core 7 which is 5.0.45 but not with 5.1.24-rc version I checked out.

The command is: ALTER TABLE m68_v2 ADD COLUMN range VARCHAR(50);

If I change range in the above to frange it works in both versions.
So is it no longer possible to create a column named range?

Write Conflict Error
I have a database with access as front end and Mysql as back end. I am
gettting the following 'Write Conflict' Error. when i am on my order
form, This form does has a subform where i enter all the products that
are ordered.

Could any one let me know what could cause this problem. I am the only
one using the database as its still in the build stage Code:

Access ODBC 'write Conflict' SOLVED!
I had this problem where a user would attempt to change a record, and
always get the 'write conflict', and that another user had changed the
record... even though no other users were on the database.

I searched high and low for a solution, to no avail. Yes, I had a
primary key; I had a TIMESTAMP field. I played around with Access'
settings for record locking, no record locking, etc.; I set the MyODBC
setting to "return matching rows". None of these worked.

Here is what I found:

I had a TIMESTAMP field in the MySQL table, NULL = OK. I imported data
from a text file into this table. This text file did not have the
TIMESTAMP field, so all of the records in the MySQL table had a
default timestamp of '00000000000000'.

I wrote a SQL statement that changed this TIMESTAMP '00000000000000'
value to an arbitrary value (for example, '20030910134340').
Everything worked fine.

MySQL 4 And MySQL 5 CURRENT_TIMESTAMP Conflict
I've been locally developing a database in MySQL 5. Now I want to put it on my server. In trying to import it, I have come across the following code which is problematic:

CREATE TABLE `physical_pages` (
`id` int(11) NOT NULL auto_increment,
`filename` text NOT NULL,
`created` timestamp NOT NULL default CURRENT_TIMESTAMP,
`lastmodified` timestamp NOT NULL default 񟍰-00-00 00:00:00',
`languageid` int(11) default NULL,
`readonly` tinyint(1) default Ɔ',
PRIMARY KEY (`id`)
);
It's my understanding that MySQL 4, which is running on my server, does not support the CURRENT_TIMESTAMP part of things. How can I modify that CREATE TABLE query to work?

More generally, is there a guide or automatic solution to moving from MySQL 5 to MySQL 4? I am going to have to install this program I'm writing on a number of servers which run MySQL 4, so would like to not have to grapple with manually editing the database dump every time I develop on my machine and update the MySQL 4 machines.

Full Text Index Before Or After Non-clustered Index
In mySQL should any required non-clusterd indexes be built before or
after a full-text index? Either on the same column or different
columns...

Index, Then Query On Only Part Of The Index
Lets say we have a fulltext index on...

column 1, column 2, and column 3

If we do a select statement matching only on column 2 and column 3 will the index still be used effectively?

Do we then need a second index only on column 2, and column 3?

Would it be smart to simply create indexes on all of the following?
column 1
column 2
column 3
column 1, 2, 3
column 2, 3

?

Howto Add Another Index To A Default Index
I have a index rang of 1-5 set as default. I am now trying to add an index 6, i know it sounds easier enough but i cannot come right. Let me past the code segment below to get a better idea of what is happening: Code:

Unique ID
Say I wanted a membership site. A user would register and get a auto created ID.. Starting from 1..

I want people to buy a User ID if they want..
Say I get the ID 567 but want to purchase the ID 5555 because I like the number. But its not created yet since there arent that many users.

Is this possible with an auto icrement ID setting for the users without messing up the flow of the auto increment field if so how would I go about it.

Unique
How can i define in mysql table that a field is unique??

Set A Unique Row
I have a website which is database driven (it's a world cup prediction league).

The problem i have is that if a user changes their predictions, the script reposts every prediction again in the database.

The database table name is:
plpredictiondata

This consists of 6 rows. The ones i am interested in are called:
userid
matchid
predtime

What i need to do, is every time that predictions are posted, the row matchid should only contain a unique number per userid. Does that make sense??

As an example, say i have 2 users (userid=1, userid=2), and 2 matches (matchid=1, matchid=2). Userid=1 posts scores for both matches, then subsequently changes them. At the moment, it will show userid=1 as having matchid=1 & 2 in the table twice. I want it to only allow the most recent prediction. I have tried using REPLACE INTO, but thats not working.

UNIQUE
I am trying to create a statement where it will return the UNIQUE values
from a table, I think its the UNIQUE statment.

So a table may have 60 entries, 20 of them are X, 20 Y and 20 Z
I want it to return only X,Y,Z e.g. 3 returned entries.

Unique Value
I have a table like this:

CREATE TABLE IF NOT EXISTS `test` (
`id` INT(11) unsigned NOT NULL auto_increment,
`cid` VARCHAR(10) NOT NULL,
`item` VARCHAR(10) NOT NULL,
`val` VARCHAR(10) NOT NULL,
`dt` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1

How do I find the unique highest value in `val` ?

Unique Row Problem
I have this statement that shows who made affiliate commisions during the month I choose.

$sql = "SELECT *, COUNT(id) AS referrals FROM user WHERE id IN (";
$sql .= " SELECT DISTINCT u.referral FROM user u, servtrans t WHERE u.id=t.userID AND t.createdOn BETWEEN '$startDate' AND '$endDate'";
$sql .= ") GROUP BY id";
I run it and it runs correctly except for the fact that if an affiliate makes a couple sales from the same person, its not showing. Its only showing one sale.
Is this because this statment is only pulling out unique id's? If so, how can i fix it?

Best Way To Do This... Unique Key But 3 Rows Need It?
I have a weekly schedule table, each week should have a unique number but each week requires 3 different records (one for each game). What would be the best to create a unique identifier that is the same for the 3 records each week?

Unique Results
I am querying my mysql database from php like so:

$query = "
SELECT $wpdb->posts.*
FROM $wpdb->posts
LEFT JOIN $wpdb->post2cat ON ($wpdb->posts.ID = $wpdb->post2cat.post_id)
WHERE $wpdb->post2cat.category_id NOT IN (2, 6)
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->posts.post_date DESC";

Its joining a second table (post2cat) as the main table (posts) does not have a value (category_id) which I need to check is not 2 or 6. As a result if an item in posts has two category ids (and so is listed twice) the item goes into my mysql result twice which I don't need. Is there a way I can return only unique results (i.e. not twice)??

This is the Wordpress mysql structure if anyone is familiar with it.

Unique Key Pair
I'm very new to MySQL, and therefore don't even know what to search for when it comes to this issue (I've tried about a dozen terms to no avail).

My table is set up like this:

CREATE TABLE `tableName` (
`sessionID` int(10) unsigned NOT NULL default 0,
`name` varchar(40) default NULL,
`value` longtext NOT NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1
`sessionID` actually corresponds to another value in a different table. From what I understand, I've set up a "one-to-many relationship" (`sessionID` must be unique in the other table, but not this one). However, I would like to add some kind of a constraint that requires that `name` is unique within the context of it's `sessionID` value.

For example, this would be allowed:

`sessionID``name``value`
1'thing1''stuff'
1'thing2''more stuff'
2'thingy''stuff'
2'thing2''blahblah'
But this would not, because there would be two identical `name` values ('thing2') for one `sessionID` value (2):

`sessionID``name``value`
1'thing1''stuff'
1'thing2''more stuff'
2'thingy''stuff'
2'thing2''blahblah'
2'thing2''error'
Is this possible, and if so, how can I state this in my CREATE TABLE query? Also, does this have a specific name (so I can look up more information if need be)?

Primary Key + Unique Key?
my question is: do I have to declare primary key and unique key in this table?

CREATE TABLE `sessions` (
`id` varchar(32) NOT NULL default '',
`access` int(10) unsigned default NULL,
`data` text,
`usr` varchar(12) default NULL,
UNIQUE KEY `id` (`id`)

Unique Query Help
I have two tables

Table 1 has 3 unique URL (for e.g. hotmail.com) and Table 2 has 4 records of (hotmail.com)...The only way to display the combination for a particular URL is to use group by.

Table 1

id url
1 hotmail.com
2 sitepoint.com
3 youtube.com

Table 2
id userid (user who added) url notes
1 user1 hotmail.com good site
2. user2 hotmail.com email site
3. user3 hotmail.com great site
4. user4 hotmail.com cool site

Now I want to JOIN table 1 and table 2 and display unique records for hotmail.com...Only way i can do is by using group by...I don't want to use that. And it doesn't really matter if it display record by any user, but as long as it is only one record for hotmail.com in Table 2

Can anyone please help me

If I use inner join from Table 1 to table 2 ON URL, it still shows 4 records of that url, though i only want to show the first one if repeat record occur.

A 'UNIQUE' Question
I have to check whether my db contains same value.
so i have altered my table as

ALTER TABLE details ADD UNIQUE (name);

so if i am giving same user name mysql will throw a false error

is it a good method ? or

should i use select query to check whether name exist or not

Unique Id Between 2 Tables
Is there a way to give a feild, lets call it "id" a unique value but without using autoincrement?

Why? I need to have 2 seperate tables ("bands" and "soloists") which both have the field "id" but there cant be a collision in the id's.

Unique Identifier
I wanted to create a unique identifier field in a mysql database, this is a fairly common practice in MS SQL, is there an equivalent in mysql?

How To Make A Unique Key 1 Again?
I want to make a unique number 1 again.

Creating A Unique Value
I'm trying to create a stored procedure which creates a unique value each time.

This one creates the a value based on what length I want to give it and prints it.
CREATE PROCEDURE `test3`(IN idlength int)
BEGIN
declare uid char(30);
declare a char(1);
set uid='';
while length(uid) < idlength do
set a= substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',floor(rand()*64), 1);
set uid = concat(uid, a);
end while;
insert into t2 values (uid);
select UID;
END $$

What I need to do is to compare this value with the already stored values in the table to check if the new one is unique. Any suggestions on how to do that?

Getting All Unique Entries
I need a query to get all unique entries in a field.

If you have:
3
3
5
4
5
3
5
5

it would return 3, 4, and 5.
Can this be done in a MySQL query.

Unique In Mysql 3.23.52
i have to use mysql 3.23.52. I have problems to create UNIQUE over two columns. Is it possible to create uniqueness over two columns? It is also not possible to create an Primary Key over two columns.

Counting Unique
I have the following code...

$query = "SELECT DISTINCT newsid FROM comments";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){

which works fine. My questions is there any way to determine which newsid shows the most amount of times. I'm trying to make a "top5" sort of things and it would make it much easier.

UNIQUE Constraint
If I have a table with a UNIQUE key 'v', trying to store upper and lower case letters should work, but won't. For example, if I insert a value of 'a' for the column v, and then try to insert a value 'A', I get an error message. Likewise, if I enter this statement:

select * from tname where v='A';

the display will include rows where v='a'.

Surely there must be a way to force MySQL to recognize the difference between upper and lower case values, and to specify that they be stored that way?

UNIQUE And INDEX Key Conflict

In phpMyAdmin, there is a warning displayed under my table stating, "UNIQUE and INDEX keys should not both be set for column `feedlist_id`". Here's my table structure:

In phpMyAdmin, there is a warning displayed under my table stating, "UNIQUE and INDEX keys should not both be set for column `feedlist_id`".

Here's my table structure: