2
votes
1answer
51 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
1
vote
0answers
55 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
0
votes
1answer
121 views

Get unmatched rows for foreign key

Consider two tables: CREATE TABLE Users( id INT PRIMARY KEY, name VARCHAR(128) ) Engine=InnoDB CREATE TABLE Tags( id INT PRIMARY KEY, user REFERENCES Users(id), tag VARCHAR(128) ) ...
1
vote
4answers
4k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
0
votes
1answer
464 views

HOW to “SUM” and “JOIN” for single query of a poll results?

I have a poll database as CREATE TABLE poll_answer answer_id, answer varchar(255), poll_id int(11), FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE, PRIMARY KEY(answer_id) ) ...
3
votes
1answer
801 views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
1
vote
1answer
519 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
9
votes
3answers
195 views

Are there any database engines which will intuit the join condition based on the existing foreign keys?

It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...
6
votes
3answers
255 views

What does the output of a JOIN statement look like?

I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use. Let's say I have 2 tables: CREATE TABLE Cities ( id INT UNSIGNED PRIMARY ...