0
votes
2answers
29 views

primary_key problem in MySql view

I have 3 tables. and I want to create a view. There is no problem about generating SQL qeury and creating a view. but when I want to use this view in my web app it says me (LIKE THIS): "your view's ...
1
vote
0answers
63 views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
0
votes
1answer
32 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
0answers
106 views

Converting UUID's from CHAR(32) to BIN(16)

I have a few questions relating to UUID's, but before I go on, let me explain our schema a bit, first, we're on MySQL 5.5 (Percona's version to be exact)... In each applicable table, we use a two ...
5
votes
1answer
223 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: ...
1
vote
1answer
47 views

one to one relationship

This is my database structure: Users {user_id, username} Profiles {profile_id, user_id, user_views} My question is: If I do it the way mentioned above, should I to set user_id as primary key or ...
1
vote
2answers
345 views

How are auto_increment keys handled in INSERT (SELECT * FROM…)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id. If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows ...
0
votes
1answer
47 views

Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

I have a complex primary key, I wonder if what I write in the title is possible: I have a look up table with 4 fields (they should compose the primary key of the table): 1) an INT column; 2) ...
1
vote
2answers
112 views

Column suitable for natural key?

I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice. I have a table that will have around 2000 definite unique values, ...
1
vote
1answer
101 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
0
votes
1answer
80 views

How to set up multiple fields as primary key in MySQL?

I have a table with fields EmployeeID blahblah blahblah2 ..... RecordMonth RecodrdYear so each employee should only have a matching entry for a month, year, Emp#. How do I set up a table. ...
2
votes
2answers
113 views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
1
vote
1answer
760 views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
1
vote
1answer
531 views

Auto Increment Primary ID inserting random values

Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird ...
0
votes
0answers
49 views

Overcoming the HDD IOPS limits on a billion row table? Is getting SSD the only solution?

I have a table with this structure: CREATE TABLE `requests` ( `userid` int(11) NOT NULL, `messageid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `problem_id` int(3) NOT NULL, ...
0
votes
1answer
98 views

primary key: username+current time or serial

I need to choose a primary key, but I'm not sure what the best option would be. Currently I'm in a scenario where a user has machines (workers) calculating hashes. For each user I track the current ...
0
votes
0answers
58 views

How to retrieve pkey value in mysql/python on duplicate key update

I'm currently using _mysql library in python. it uses pkey(int, auto increment) value that's created when inserting data using function conn.insert_id() This is how the query is made. INSERT INTO ...
1
vote
1answer
151 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
0
votes
3answers
353 views

Performance of primary key IDs with gaps (but in sequential order)

I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e: 154300000 283700000 351300000 464200000 ...will ...
0
votes
2answers
3k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
0
votes
1answer
60 views

How to arrange rows selected in MYSQL query?

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) . I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A ...
1
vote
1answer
136 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | ...
0
votes
1answer
111 views

Use surrogate or natural PK if a row is uniquely identified by two or more columns?

I am having a hard time choosing between natural and surrogate PK for my database table. The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go ...
7
votes
1answer
4k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
2
votes
2answers
654 views

Two primary keys to one foreign key

racing_couple ID (PRIMARY) breeding_couple ID (PRIMARY) egg IDpar(FOREIGN KEY) I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) ...
2
votes
1answer
859 views

prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index. MySQL accepts this query: CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
28 views

NEWB Getting to KNow New Database and Understand Where Relationships Are

I'm teaching myself SQL using MYSQL and a textbook. While working through the textbook I'm using a database I'm kinda familiar with - an exported copy of my CMS driven websites database. My website ...
2
votes
1answer
280 views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
0
votes
2answers
664 views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
0
votes
1answer
674 views

Changing primary key from varchar to int

I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key. So I would like to change my primary ...
1
vote
1answer
257 views

Would Mongo's ObjectID work well in InnoDB's clustered index?

Mongo's ObjectID is defined like this: ObjectId is a 12-byte BSON type, constructed using: a 4-byte value representing the seconds since the Unix epoch, a 3-byte machine identifier, ...
2
votes
2answers
120 views

What can I do to make mysql use the expected indices?

What can I do to make mysql use the expected indices? I've got 4 tables, two containing resources, and the others containing historical changes. One pair uses indexes correctly, the other doesn't, ...
0
votes
1answer
22 views

Which of tables key to reference?

MySQL. There is a table users with autoincrement primary key id. There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid ...
2
votes
2answers
229 views

MySQL - Normalization over meaningless data - Compound Primay Keys or Surrogate ID field

Please forgive my title; if you have a suggestion for it, feel free to comment. I have a database: DROP TABLE IF EXISTS `books`; CREATE TABLE `books` ( `isbn` VARCHAR(255) NOT NULL, `title` ...
1
vote
1answer
135 views

Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
2
votes
1answer
1k views

Unique insert performance: Primary Key vs Unique Index

I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records. What's the fastest way to insert into the table, by keeping the domain unique ...
0
votes
1answer
208 views

Maintain table with too many insert

I have to create a table. Its size is going to increase very fast. Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for ...
1
vote
1answer
529 views

does last_insert_id() returns correct result when writes from multiple sessions?

I have a table in which there is a combination of 4 columns that will act as a unique key. Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on ...
0
votes
1answer
305 views

MySQL non-sequential primary key of certain length

what's the best way to create a non-sequential primary key of a certain length in MySQL? I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
1
vote
1answer
695 views

What should one do if the auto increment primary key exceeds the maximum?

I have a primary key for a table with the following structure, **Field** **Type** nid int(10)unsigned I know the maximum limit of int(10) is 4294967295. But What ...
1
vote
1answer
148 views

Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I ...
2
votes
3answers
2k views

Does the MySQL primary key affect the efficiency of a SELECT query?

Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of ...
1
vote
5answers
2k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
4
votes
3answers
209 views

Most efficient (or meaningful) way to index grid-based data

I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
9
votes
4answers
7k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
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?
1
vote
1answer
1k views

Does the order of keys in compound Primary key make any difference?

Package Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion ...
1
vote
1answer
2k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
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
1answer
961 views

updating mysql primary keys

I need to be able to swap values that are used in a primary key. Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1. In other relational databases, (Sql Server and ...