Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:
CREATE VIEW category_url AS
( Query
) ;
If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag
table, as long as the foreign key constraints are defined:
SELECT DISTINCT
ct.category_id,
ut.url_id
FROM
category_tag AS ct
JOIN
url_tag AS ut
ON ut.tag_id = ct.tag_id
or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:
SELECT
ct.category_id,
ut.url_id,
COUNT(*) AS number_of_joining_tags --- optional
FROM
category_tag AS ct
JOIN
url_tag AS ut
ON ut.tag_id = ct.tag_id
GROUP BY
ct.category_id,
ut.url_id
If you also want columns from the category
and url
tables as well (which is very probable), you need to join those tables, too:
SELECT
ct.category_id,
ut.url_id,
COUNT(*) AS number_of_joining_tags, --- optional
c.*, --- the columns from `category`
u.* --- and `url` that you need
FROM
category_tag AS ct
JOIN
url_tag AS ut
ON ut.tag_id = ct.tag_id
JOIN
category AS c
ON c.id = ct.category_id
JOIN
url AS u
ON u.id = ut.url_id
GROUP BY
ct.category_id,
ut.url_id
If you are determined to retain your existing table structure, the best indexes will be something like:
create index ix1 on fp_core(hashkey, note_id, timeoffset);
create index ix1 on fp_core_keys(hashkey);
You mention that the hashkeys can be different for the same song, but it sounds like you have some mechanism for detecting similar songs - if possible, add an additional column called 'songid' to your fp_core table and then query on this single value using a WHERE clause.
Best Answer
First, try putting together the ids in a query (a tableless query)
Watch it work...
Now, JOIN that tableless query to the data table
or
Give it a Try !!!