Tutorial :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!


Solution: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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »