0

Case & Problem:

Consider a corporate building with some rooms and departments that need authorization to enter.
Employees have to scan their badges to be authorized.
If the employee is inside (the badge was already scanned), the system warns her/him accordingly and does not open the door.
And the supervisor should be able to see and/or report all employees who are in a particular room at any given time.

Checking whether the employee is inside or not was somewhat trivial with the help of a couple of StackOverflow/StackExchange answers.

My problem is at the reporting stage.
The question is how can I filter the table to show those who are currently inside the given point, in a performant way.

Current Situation & What I've Done So Far:

My backend code is looping over all the records and check if they're inside, one by one.
Yet, I suspect that this is not ideal (I might be wrong, though) performance-wise and/or for modularity/responsibility concerns; in the future, I'll need to allow other client applications to connect to the database without having them write their own code/logic to check or report the inside status.

Sample Table:

+-----+----------+---------------+-----------+-----------------+
|  id | code     | point         | action    | timestamp       |
+-----+----------+---------------+-----------+-----------------+
|   1 | 11111111 | Department A  | pass_in   | 1561369024008   |
|   2 | 11111111 | Department A  | pass_out  | 1561369024007   |
|   3 | 11111111 | Department A  | pass_in   | 1561369024006   |
|   5 | 22222222 | Department B  | pass_in   | 1561369024005   |
|   6 | 22222222 | Department A  | pass_out  | 1561369024004   |
|   7 | 22222222 | Department A  | pass_in   | 1561369024003   |
+-----+----------+---------------+-----------+-----------------+

Sample Code:

var point = 'Department A';
var recordsInPoint = helper
                        .fetchAllRecords()
                        .filter( record => helper.isInside( record, point ) );

Query to check if inside:

SELECT
    ( count_compare.count_in > count_compare.count_out ) AS is_inside
FROM
(
    SELECT
        (
        SELECT 
            COUNT(`id`)
        FROM 
            scanner_log
        WHERE 
            `code`='11111111' AND 
            `point`='Department A' AND 
            `action`='pass_in'
        )  AS count_in,
        (
        SELECT 
            COUNT(`id`)
        FROM 
            scanner_log
        WHERE 
            `code`='11111111' AND 
            `point`='Department A' AND 
            `action`='pass_out'
        ) AS count_out
) AS `count_compare`;

Questions:
• How can I move the reporting logic from backend to database?
• Would it be really faster and less resource-hungry if handled by db server?

Ideas & Options:

• Create a stored procedure and carry the backend logic to db as is by looping over all records.
I don't see much benefit on this, to be honest. Same loop method, just performed by db server. Also, there's a major drawback as I don't have just one table for logs, there are separate tables for each building or company. And I can't pass the table name as a parameter, as far as I know mySQL / MariaDB doesn't support this. I have to use PREPARE QUERY and EXECUTE QUERY commands which sounds a little bit overkill to me.

• Create another nested temporary table and join them somehow to filter but I just couldn't get my head around it, really, nothing.

Thanks for all your help and insights.

2
  • "looping over all the records..., one by one". -- That's thinking like a programmer. Learn to think like a DBA, where a single SQL query can act on an entire table. That is the primary way to get performance. – Rick James Jun 24 '19 at 22:38
  • @RickJames, I agree, I got to learn to think more like a DBA when it comes to DB stuff and I'm trying to :) – Ender Nafi Jun 25 '19 at 7:01
0

In a situation like this one, I recommend having two tables:

  • History (like what you have). This is a list (a la audit trail) of everything that has happened (INs and OUTs).
  • Current -- The latest place where each person is.

History has an ever-growing number of rows, and is probably not relevant for the Question at hand.

Current contains one row per person. A simple SELECT with a WHERE clause should pick out all the persons that are "IN" (or "OUT").

"separate tables for each building or company" -- No. This is the third time today I have advised against having multiple identical schemas.

On the other hand, Current and History may have the same columns. This is an exception to the previous paragraph.

Do not split by department or building.

7
  • About the multiple identical schemas, each logs table will have about 8-9 million rows per year and there are more than hundred buildings. If combined, one table will have to handle hundreds of millions logs per year. From this perspective, isn't it better to separate these? Especially, given that I'm a novice when it comes to db management; I know next to nothing about sharding and other fancy stuff. Now, I'm hearing you say that then go learn stuff or find someone who knows :)) – Ender Nafi Jun 25 '19 at 7:18
  • @EnderNafi - you have manually "partitioned" the data. And you are living with the hassles of constructing the right table name for each query. Although having a single table would be simpler, I don't necessarily recommend it now, since there would be pain in doing the transformation. – Rick James Jun 25 '19 at 17:00
  • @EnderNafi - My Answer assumed are just starting out and did not already have a billion rows, so I was pushing you in what I think is the optimal direction. Your Question sounded like a "groupwise-max" problem, which is messy to do and likely to perform poorly -- The History/Current split addresses performance there. Are there other queries that should be taken into consideration? With a target of a billion rows, we need to look at all queries together. – Rick James Jun 25 '19 at 17:06
  • Splitting by building or department provides virtually no performance. Ditto for PARTITIONing. How much data do you already have? – Rick James Jun 25 '19 at 17:09
  • Firstly, I've created a current table as you suggested and made necessary changes to other parts of the application; it works great, so far. Second, the application is not in full production-mode, just pilot (testing) departments. That is, all data I have currently is expendable which is about 100k rows of logs ( history ) and much less in other tables. Third, each scan (and getting a response) requires 5 queries at worst and still I can get a response in under 40ms. including the network-latency. Processing of the backend takes usually 6-7ms. – Ender Nafi Jun 25 '19 at 20:06

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.