Have Geek Brain

Indexing MySQL Queries Using a Timestamp Structure

I was wondering what the best way to store custom timestamp related queries in MySQL was. Let's say I only have two inputs, a custom "request" and a "timestamp" ...

I could create a MySQL table with fields ( id

, query

, count

, timestamp_list

), where:

id

is the unique id of the request,
query

is the literal query string,
count

is (constantly-UPDATEd) the number of queries that are being entered, and
timestamp_list

is LONGTEXT or something with a list of queries that were searched.

Is there a better way to match them using indexing that I'm not familiar with? It seems that storing a list of timestamps in LONGTEXT is dumb but easy; maybe i should create a separate table like:

id


query_id

(correlates with id

in the first table)
timestamp

And I can combine the results with the first table. Any suggestions? Thanks!

0


a source to share


1 answer


If you need to record the timestamp of when each query was executed, I would assume that you have two tables:

 tbl_queries
  - id       INT
  - query    VARCHAR

 tbl_queries_performed
  - id         INT AUTOINCREMENT
  - query_id   INT
  - timestamp  CURRENT_TIMESTAMP


Every time you want to record a query, check if it is in tbl_queries

and then save the record in tbl_queries_performed

with query_id accordingly

+3


a source






More articles:


All Articles