1BgQTk0W

1DROP TABLE IF EXISTS users_logs;
2DROP TABLE IF EXISTS users;
3
4create table users
5(
6	id INT
7		constraint users_pk
8			primary key,
9	email varchar(255) not null -- unique login in the system
10);
11
12create table users_logs
13(
14	id serial
15		constraint users_logs_pk
16			primary key,
17	log TEXT,
18	user_id int,
19	created_at timestamp,
20	FOREIGN KEY (user_id) REFERENCES users (id)
21);
22
23CREATE INDEX idx_user_id 
24ON users_logs(user_id);
25
26
27
28INSERT INTO users (id, email) VALUES (1, '1@test.com');
29INSERT INTO users (id, email) VALUES (2, '2@test.com');
30INSERT INTO users (id, email) VALUES (3, '3@test.com');
31-- ...another 1m users
32
33INSERT INTO users_logs (id, log, user_id, created_at) VALUES (1, 'error', 1, '2019-05-20 08:15:30.258000');
34INSERT INTO users_logs (id, log, user_id, created_at) VALUES (2, 'warning', 2, '2019-05-20 08:19:15.300000');
35INSERT INTO users_logs (id, log, user_id, created_at) VALUES (3, 'notice', 3, '2019-05-20 08:19:37.022000');
36INSERT INTO users_logs (id, log, user_id, created_at) VALUES (6, 'error 2', 1, '2019-05-20 08:15:30.258000');
37INSERT INTO users_logs (id, log, user_id, created_at) VALUES (7, 'err', 2, '2019-05-20 08:20:10.021000');
38INSERT INTO users_logs (id, log, user_id, created_at) VALUES (8, 'error 3', 3, '2019-05-20 08:15:30.258000');
39
40select users_logs.*
41
42from users 
43INNER JOIN users_logs ON users_logs.user_id = users.id
44
45where users.email='2@test.com'
46order by users_logs.created_at desc
47limit 10