How can I count some rows before inserting? 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 &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
                 count(date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
          from QANDA  
          where author_id = :id and
               type = 0 and
               date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
         ) a 
    WHERE num_month &lt; 50 and num_day &lt; 6 and num_90min &lt; 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 &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 day))) as num_day,
               count(date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))) as num_90min
        FROM QandA
        WHERE author_id = :id and
              type = 0 and
              date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 50 day))
        HAVING num_90min  &gt; 50 
            OR num_day    &gt; 6 
            OR num_month &gt; 1 
    )


Ok, which one is better? Or is there a third option which is better than them?
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() ) &gt;= 6  -- disallow
-- How many "since midnight yesterday" (overkill; see below):
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day &gt;= CURRENT_DATE() - INTERVAL 1 DAY ) &gt;= 6  -- disallow
( SELECT SUM(ct) FROM Summary WHERE author_id = :id
       AND day &gt;= CURRENT_DATE() - INTERVAL 50 DAY ) &gt;= 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 &gt; NOW() - INTERVAL 1 DAY ) &lt; 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)