MySQL query indexing with timestamp structure

Question

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!

duckyflip · Accepted Answer · 2009-05-20 10:29:50Z

3

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

answered May 20, 2009 at 10:29

duckyflip

16.5k5 gold badges36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query indexing with timestamp structure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related