I have the following two-table setup:
CREATE TABLE relationships ( id bigint(20) NOT NULL AUTO_INCREMENT, type varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id), UNIQUE KEY id_type (id,type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE content_relations ( relationship_id bigint(20) NOT NULL, site_id bigint(20) NOT NULL, content_id bigint(20) NOT NULL, PRIMARY KEY (relationship_id,site_id,content_id), KEY relationship (relationship_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
The currently used indexes are included, but that's exactly my question:
Given the above tables and the following queries, what indexes should one use (instead)?
For the first table only, the following queries are performed (each with the given frequency):
SELECT type FROM relationships WHERE id = X;
DELETE FROM relationships WHERE id = X;
For the second table only, the following queries are performed (each with the given frequency):
SELECT site_id, content_id FROM content_relations WHERE relationship_id = X;
DELETE FROM content_relations WHERE relationship_id = X AND site_id = Y;
DELETE FROM content_relations WHERE relationship_id = X;
SELECT DISTINCT relationhip_id FROM content_relations WHERE site_id = X;
Frequency: very low
Lastly, there is the following query using both tables:
SELECT r.id FROM relationships r INNER JOIN content_relations cr ON r.id = cr.relationship_id WHERE cr.site_id = X AND cr.content_id = Y AND r.type = Z LIMIT 1;
Frequency: very high
The first table (i.e.,
relationships) has an auto-incremented (and thus unique) column
id, so there is the
PRIMARY KEY (id). I guess that's okay. ;)
Both queries using this table only include just the primary key's column, so that's very good already.
Then there is the query using both tables, which also includes the
type column. Does it make sense to use the unique composite
KEY (id,type) here? Or is it redundant, or at least not of great advantage? Please keep in mind that there are just a few different values for the
The second table (i.e.,
content_relations) currently has a
PRIMARY KEY that consists of the (unique) combination of all three columns. This key is used for the last query using both tables, and also serves as constraint, because for every relationship(_id) and site(_id) there can only be one (or no) content element with a specific ID.
There are two queries using just the
relationship_id column in the
WHERE clause, that's why there is the
Then there is one query that also includes the
site_id column. Should one therefore add the composite
I suppose there is no need for the
KEY (site_id), which would only be used by a single, very infrequent query, right?
KEY (relationship_id) is basically a foreign key to the
relationships table. However, as I am working in an environment that allows for MySQL versions/engines that do not include foreign key constraints, I cannot use a real
FOREIGN KEY, and all its benefits. But this shouldn't be relevant for my question(s), right?
// EDIT: Here is a little more context.
I didn't put it in the question before just because it was already pretty bulky. Since an answerer asked for more, I will do this now, though. Be prepared, however, it's quite a lot information.
The tables are about relationships between content elements (CE). Each CE has an ID and a type. For each type, there is only one CE with a specific ID. There may, however, be CEs
B (with types
B), and both have the same ID.
Relations are allowed between two or more CEs that A) have the same type, and B) are from different sites (represented by their unique ID). That's why the
type column is part of the
relationships table (rather than being a column in the
Sites are stored in another table, which I cannot alter in any way. I just use the sites' unique IDs, and that's all.
The CEs are stored in type-specific tables, which I also cannot alter in any way. I just use a CE's ID and its type, and thus have a unique identifier.
AUTO_INCREMENT for the
id column in the
relationships table is used, because there are lots of relationships for a specific type, and I need a unique identifier for every relationship. When inserting a new relationship for a given type, I just have to provide the
type, and automatically get a unique
id. That's what
AUTO_INCREMENT is for, isn't it? Or did I misunderstand you, @Rick James?
I guess I don't really need
bigint. As I reference other tables' columns, however, and as these columns are defined as
bigint(20), I thought it okay, if not wise, to use the same definition.
There are two usages of
LIMIT 1. For the first one, there shouldn't even be more than one entry (because I query for
id and compare
(id,type) is unique). So yes, I could/should remove this, thanks. The second time, however, there might be several entries - all with the same
relationship_id. That's why I happily stop if I got one result.
Is there anything else I should provide you with?
Here is a visual representation of what this is all about:
The individual figures represent individual content elements, while their individual type is represented by their individual shape, and their ID is written inside. Each color represents a relationship (i.e., a group of related content elements). As you can see, for any color (i.e., relationship), the shape (i.e., type) is the same, while there is not more than one figure (i.e., content element) from each site.
I hope this makes it more clear - and not more confusing.