MySQL query indexing with timestamp structure

Question

Hi all,

I was wondering what the best way of storing user queries correlated with timestamps in MySQL was. Let's say I have just two inputs, a user's "query" and "timestamp"...

I could create a MySQL table with fields (id, query, count, timestamp_list), where:

id is unique identifier of the query,
query is the literal query string,
count is the (constantly-UPDATEd) number of times that query is entered, and
timestamp_list is a LONGTEXT or something with a list of times that query was searched.

Is there a better way to correlate these using indexing I'm not familiar with? It seems like storing a list of timestamps in a LONGTEXT is dumb, but easy; perhaps I should create a separate table like:

id
query_id (correlates to id in first table)
timestamp

And I can join results with the first table. Any thoughts? Thanks!

Answer 1

If you need to record the timestamp when each query was performed, i'd suggest you have 2 tables:

 tbl_queries
  - id       INT
  - query    VARCHAR

 tbl_queries_performed
  - id         INT AUTOINCREMENT
  - query_id   INT
  - timestamp  CURRENT_TIMESTAMP

Each time you want to record a query, check if it's in tbl_queries already and then save an entry in tbl_queries_performed with the query_id respectively