StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSorting and calculating by dates in joined query
text
Body
copied!<p>I am doing a project where I am tracking users on a website, I log their every hit across the site. Whenever they hit a URL, I will create it in the database, and tag it with some tags. </p> <p>Every URL is named a 'resource' in my database, and a resource can be tagged with multiple tags. A Visitor is connected to resources when they visit a URL, and when a user hits a resource, i also connect the date to it.</p> <p>The thing I want to do is finding the resources with the correct tags, that has been watched this month or today for an example.</p> <p>The query I am currently building is here:</p> <pre><code>SELECT r.resource_id, r.resource_url FROM resource r JOIN visitor_resource vt ON vt.resource_id = r.resource_id JOIN resource_tags rt ON rt.resource_id = vt.resource_id JOIN tags t ON t.tag_id = rt.tag_id AND t.tag_name = '42' GROUP BY r.resource_id </code></pre> <p>To give you an idea for the structure you can see here: <a href="http://kaspergrubbe.dk/db-overview.png" rel="nofollow noreferrer">tracking database structure http://kaspergrubbe.dk/db-overview.png</a></p> <p>So basically I will have to count how many visitor_resources there is in a given month by looking at visitor_resources.last_visited for the last month, and get the 5 most visited resources.</p> <p>How to approach this? </p> <p>The above query also seems very slow without query-caching, I suspect it is because t.tag_name is not an index, and that is a varchar, but is there anyway to speed up the process other than adding this index?</p> <p>Thanks.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload