0
votes
2answers
51 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
46 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
1
vote
1answer
19 views

Duplicate Data from two column

i have a table which has only two column one is pid(Photo ID) and another one is tid(Tag ID) +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
133 views

MySQL 5.1 replication broken after crash, duplicate key error, why? No redo log corruption

What do you think is the reason for the duplicated key errors in our MySQL 5.1.61 replication slave, after the slave machine crashed? It's not corrupted redo logs. Details follows: We have ...
5
votes
1answer
224 views

Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY. The SHOW CREATE TABLE: ...
2
votes
2answers
600 views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
2
votes
2answers
150 views

MySQL - unique constraints design

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community. Please consider the diagram: I would like to add a unique ...
1
vote
1answer
138 views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
1
vote
1answer
48 views

Is it better to add an index and search via the index or add a unique constraint and let an insert fail?

I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job: mysql> DESCRIBE job_views; ...
0
votes
2answers
189 views

Unique indexed column in where clause and mysql execution after finding first row

Suppose in a table there are 2 columns: login and password login is indexed as unique index, password not indexed. query: SELECT * FROM mytable WHERE login = 'Jhon' query: SELECT * FROM mytable ...
2
votes
1answer
217 views

SQL Uniqueness Across Multiple Tables

I have two SQL tables: Users and clients. Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field. The users ...
1
vote
0answers
95 views

mysql update unique values from 2 different tables based on 2 index fields

i have 2 tables with the same unique id. i want to change table1 unique_id sequence from (1-100) to random 8 digit ids (43342359 - 83083224) and cascade that to table2. what i have: - table1 id1 ...
5
votes
2answers
274 views

Resolving DUPLICATE for a column with UNIQUE

I have a few column with UNIQUE INDEX. In some queries, duplicates should be acceptable, in which a postfix should be added to the value. For example, if test title exits, we change it to test ...
2
votes
1answer
368 views

MySQL Unique Index Keylength

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key. The engine of choice here is InnoDB. There are ...
1
vote
0answers
120 views

How can I force myisamchk to modify the original data file in case of duplicate keys?

In the documentation for myisamchk, it says: --quick, -q Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to ...
2
votes
3answers
1k views

Unique combination key MySQL

Lets say I have a table with 3 fields: id1 id2 mapTypeId What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database: ...
6
votes
2answers
7k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
1
vote
1answer
3k views

How to use ON DUPLICATE KEY for UPDATE

Consider a table of CREATE TABLE test ( id int(11) unsigned NOT NULL AUTO_INCREMENT, external_id int(11), number smallint(5), value varchar(255), UNIQUE INDEX (external_id, number), PRIMARY KEY(id) ...
5
votes
3answers
3k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
3
votes
1answer
189 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
2
votes
4answers
1k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
1
vote
2answers
1k views

MySql - partition by range and unique key

i'm trying to create the following table: CREATE TABLE `s_relations_with_partition` ( `id` int(11) NOT NULL AUTO_INCREMENT, `source_persona_id` int(11) NOT NULL, `relation_type` int(11) NOT ...
3
votes
2answers
2k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
2
votes
1answer
5k views

How can I optimize my mysql setup to create my index faster?

I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored. I have an MyISAM like ...
3
votes
1answer
767 views

Duplicate UNIQUE KEY error after ROLLBACK in MySQL

I have a table like this mysql> describe seudonimos; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
6
votes
3answers
3k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...