# Effectively logging search queries
**URL:** https://meta.discourse.org/t/effectively-logging-search-queries/63838
**Category:** Feature
**Created:** [2. Juni 2017 um 20:29 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838 "2017-06-02T20:29:35Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2. Juni 2017 um 20:29 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/1 "2017-06-02T20:29:36Z")
For 1.9 we plan to add a search log.
Unfortunately, the “log every search the server makes” approach is incorrect as we perform searches as people are typing which will result in a massively noisy log.
### Proposal
- Create a new table
term, user\_id (nullable), ip\_address, created\_at, clicked\_topic\_id (nullable), source\_id (either header or fullpage)
- Log on server with the following algorithm on search
```plaintext
UPDATE term
SET term = :new_term
created_at: :now
WHERE created_at < 5.seconds.ago AND
position(term in :new_term) = 0 AND
(user_id = :user_id OR ip_address = :ip_address)
term: new_term,
now: Time.zone.no,
ip_address: request.ip
If update touches zero rows, then insert a **new** search log row
```
Or, in English
- Update existing search log row IF:
- On click on search result (in either full page search or header) update the `clicked_topic_id`, (have search results return log id, then update it based on log id + user match + in last 10 minutes)
### Limiting log size
So the log does not grow forever there should be a site setting for maximum rows to store. Default should be about a million.
A weekly job can delete oldest rows or something.
Thoughts? Feedback?
---
### Author:  [@elijah](https://meta.discourse.org/u/elijah)
#### Post date: [8. Juni 2017 um 04:11 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/2 "2017-06-08T04:11:58Z")
> [@sam](#):
>
> On click on search result (in either full page search or header) update the clicked\_topic\_id, (have search results return log id, then update it based on log id + user match + in last 10 minutes)
So if I search for something and open three results in three tabs, what happens? Last one “wins”?
➡ The behavior of “multiple clicks for one search, save last only” is better for times when someone searches, clicks, hits back, clicks again, hits back, clicks a final time. New tabs subvert that though.
If I search for a topic, right click to copy URL, and then paste that in as a “you should look here” answer, is that counted as a “click”?
➡ Copying URLs from posts does not increase click count, for reference. But this is a case when the desired search result _really_ should be saved.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [11. Juli 2017 um 02:01 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/3 "2017-07-11T02:01:22Z")
Hopefully we can make a little progress on this, maybe this week @eviltrout?
---
### Author:  [@eviltrout](https://meta.discourse.org/u/eviltrout)
#### Post date: [14. Juli 2017 um 18:34 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/4 "2017-07-14T18:34:12Z")
I’ve implemented this mostly as specified in the original post:
[https://github.com/discourse/discourse/commit/97e211f8372993622698aceb3eeb801a150aa9e5](https://github.com/discourse/discourse/commit/97e211f8372993622698aceb3eeb801a150aa9e5)
I also went in and replaced all the mock tests with [integration tests](https://github.com/discourse/discourse/commit/d7f783ffed826b832f43ad438648cc7e74e6f7aa) and added a separate commit for the [clean up](https://github.com/discourse/discourse/commit/6b6ad9391b2c89b4609550a4f2d7222efee888be) job.
---
### Author:  [@eviltrout](https://meta.discourse.org/u/eviltrout)
#### Post date: [14. Juli 2017 um 19:57 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/5 "2017-07-14T19:57:57Z")
Actually I forgot about the click tracking 🙂
Sorry it’s been a difficult couple of days for me, I’ll get it in on Monday.
---
### Author:  [@eviltrout](https://meta.discourse.org/u/eviltrout)
#### Post date: [17. Juli 2017 um 19:52 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/6 "2017-07-17T19:52:34Z")
This commit adds click tracking support:
[https://github.com/discourse/discourse/commit/cdb3706025b5c7b233ec7a36f3c0ac29d6970254](https://github.com/discourse/discourse/commit/cdb3706025b5c7b233ec7a36f3c0ac29d6970254)
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [9. Dezember 2017 um 07:27 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/7 "2017-12-09T07:27:12Z")
May someone please explain how the following columns in the current `search_logs` table work?
- search\_result\_id
- search\_type
- search\_result\_type
Also, it would be useful if all columns contained detailed description, and those descriptions were shown in the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin, here:
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [9. Dezember 2017 um 07:54 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/8 "2017-12-09T07:54:18Z")
I’m not sure what you mean by “how they work” but as far as for the “descriptions” those are “column definitions” or “schema information” and are recognizable as such for those experienced with database tables.
To gain an insight on how they “work” you can look at the tables corresponding model. For example:
>
and near the top of that file you’ll see
>
From there you can search for variable and function names etc to find other files that interact with the table (a good IDE or text editor helps a lot).
Does that help any?
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [9. Dezember 2017 um 08:08 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/9 "2017-12-09T08:08:08Z")
> [@Mittineague](#):
>
> I’m not sure what you mean by “how they work”
Yeah, sorry for being unclear. By this I just mean “what they mean”.
A description for every column would be useful in database schema though.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [9. Dezember 2017 um 08:20 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/10 "2017-12-09T08:20:38Z")
@techAPJ was the last to work on this, so maybe he can comment.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [9. Dezember 2017 um 08:24 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/11 "2017-12-09T08:24:53Z")
I guess I may be peculiar, but for me the definitions in combination with good descriptive field names are what I’m used to reading when I read schema.
It requires a shift from reading English to reading code, and I have doubts that “translating” code to English would be practical if even possible. That said, it is often easy enough to explain a small piece of code to answer a specific question.
---
### Author:  [@techAPJ](https://meta.discourse.org/u/techAPJ)
#### Post date: [9. Dezember 2017 um 08:58 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/12 "2017-12-09T08:58:25Z")
> [@meglio](#):
>
> search\_type
> [@meglio](#):
>
> search\_result\_type
I hope the code @Mittineague linked above explains what they mean.
> [@meglio](#):
>
> search\_result\_id
Primary ID of `search_result_type` entity.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [28. Dezember 2017 um 00:56 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/13 "2017-12-28T00:56:54Z")
This is done and dusted, closing.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [28. Dezember 2017 um 00:56 UTC](https://meta.discourse.org/t/effectively-logging-search-queries/63838/14 "2017-12-28T00:56:56Z")