1

Here is my table structure:

// QandA
+----+----------------------------------------+------+---------+-----------+------------+
| Id |                   body                 | type | related | author_id | date_time  |
+----+----------------------------------------+------+---------+-----------+------------+
| 1  | content of question1                   | 0    | null    | 12345     | 1467468795 |
| 2  | content of first answer for question1  | 1    | 1       | 53456     | 1467469311 |
| 3  | content of question2                   | 0    | null    | 43634     | 1467469512 |
| 4  | content of second answer for question1 | 1    | 1       | 43665     | 1467470098 |
| 5  | content of first answer for question2  | 1    | 3       | 43324     | 1467471291 |
+----+----------------------------------------+------+---------+-----------+------------+
/* Columns:
   type: 0 means question, 1 means answer
   related: null means question, else it's the id of its own question
*/

I'm trying to implement this (something exactly like what stackoverflow does). An user can ask:

  • 1 question per 90 minute
  • 6 question per day
  • 50 question per month

So I need to count the number of previous question for an user before letting him to ask. How can I do that?


I have two kind of query:

One:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
    SELECT NULL, :body, 0, NULL, :id, unix_timestamp
    FROM (select count(*) as num_month,
                 count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
                 count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
          from QANDA  
          where author_id = :id and
               type = 0 and
               date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
         ) a 
    WHERE num_month < 50 and num_day < 6 and num_90min < 1;

Two:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM dual
WHERE user_id = :id 
   AND NOT EXISTS (
        SELECT count(*) AS num_month
               count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
               count(date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
        FROM QandA
        WHERE author_id = :id and
              type = 0 and
              date_time > unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
        HAVING num_90min  > 50 
            OR num_day    > 6 
            OR num_month > 1 
    )

Ok, which one is better? Or is there a third option which is better than them?

7
  • Please provide the output of SHOW CREATE TABLE QandA/cookie\G.
    – Vérace
    Jul 3 '16 at 14:39
  • @Vérace That was a typo, there isn't cookie table .. I've Edited ..
    – stack
    Jul 3 '16 at 14:40
  • And can you put the output of SHOW CREATE TABLE QandA\G?
    – Vérace
    Jul 3 '16 at 15:02
  • @Vérace Well I'm not sure why do you need this for ..
    – stack
    Jul 3 '16 at 15:12
  • I meant the text - so it can be copied and pasted. In general, it's a bad idea to post graphics of text output. And your graphic appears to have been cropped.
    – Vérace
    Jul 3 '16 at 15:17
2

Counting and subqueries are somewhat costly; let's try to avoid them.

  • If the latest question is less than 90 minutes ago, disallow. This involves MAX() and an index on the timestamp column.
  • If 6 or more today (or today+yesterday), disallow. This involves a summary table, see below.
  • If 50 or more ... This involves the same summary table, plus a SUM() of only 30 rows, not 50, in worst case. (OK that does not help much.)

Summary table something like...

CREATE TABLE Summary (
    author_id ...
    day DATE NOT NULL,
    ct TINYINT UNSIGNED,
    PRIMARY KEY(user_id, day)
) ENGINE=InnoDB;

INSERT INTO Summary (author_id, day, ct)
    VALUES (:id, CURRENT_DATE(), 1)
    ON DUPLICATE KEY UPDATE ct = ct+1;

-- How many "since midnight":
( SELECT ct FROM Summary WHERE author_id = :id
       AND day = CURRENT_DATE() ) >= 6  -- disallow
-- How many "since midnight yesterday" (overkill; see below):
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day >= CURRENT_DATE() - INTERVAL 1 DAY ) >= 6  -- disallow
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day >= CURRENT_DATE() - INTERVAL 50 DAY ) >= 50  -- disallow

Sinece the second only checks for "6 in the last 2 days", it needs this to further check:

-- Exact count since this time yesterday:
( SELECT COUNT(*) FROM Qanda WHERE author_id = :id
       AND timestamp > NOW() - INTERVAL 1 DAY ) < 6

The query against the summary table should be faster, hence is probably worth doing as a quicker check.

I would define the limit as "6 since midnight" in order to simplify and speed up the checks.

Qanda needs INDEX(author_id, timestamp)

2
  • What do you mean user_id in this index? PRIMARY KEY(user_id, day) ? Do you mean author_id?
    – Martin AJ
    Aug 27 '16 at 8:49
  • @MartinAJ - The question is about limiting what a "user can ask". So, I assume that user_id identifies a "user". The Summary table keeps track of how many questions each user_id has asked for each day (hence the PK). From this, my query against Summary can quickly compute the day-related restrictions.
    – Rick James
    Aug 27 '16 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.