Tagged Questions
1
vote
1answer
26 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
73 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
66 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
39 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 ...
0
votes
0answers
166 views
Can't UPDATE record with unique key [closed]
Im trying to do an UPDATE statement.
UPDATE `users` SET
`Email` = 'email@domain.com',
`Mobile` = '123456789',
`City` = 'Chicago',
`Firstname` = 'John',
`Lastname` = 'Smith',
`DOB` = '1980-01-01',
...
5
votes
2answers
107 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
165 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
71 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
621 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:
...
3
votes
2answers
3k 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
1k 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
784 views
Differences between “Unique Key” and “Primary Key”
What are the major differences between Unique Key and Primary Key in MySQL?
3
votes
1answer
134 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
606 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
573 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
768 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 ...
1
vote
1answer
3k 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
594 views
Duplicate UNIQUE KEY error after ROLLBACK in MySQL
I have a table like this
mysql> describe seudonimos;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra ...
5
votes
3answers
2k 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 ...
