Say you want to optimize a query in a postgres database like: SELECT DISTINCT ON (first) first, second, third FROM my_table WHERE second > 100 AND fourth = 3 ORDER BY first, second DESC, third DESC (EDIT: In this example,…
Context: I've been creating a database for a class assignment, the assignment doesn't have detailed instructions, instead gives us a narrative in order to have us make our own assumptions and interpretations as to what to create for the database.…
I am quite new with Redis and cache memory and I am bit confused when searching on the official documentation and previous stackoverflow responses . My use case is very simple : I have to use redis to store a…
If I have two queries with the same field but different sort fields then, do I need to create a single compound index or multiple compound index with different sort fields? I am working on an e-commerce site, where I…
I have a table tbl in Postgres with 50 million rows. The tbl has an index on column_1 and there are a lot of queries to this table like select * from tbl where column_1 = 'value' Each query returns…
I have a simple MySQL table which consists of a primary key ID field, a timestamp (integer) field, and a foreign key ID field (device_id). There are indexes on each of these columns: mysql> show indexes from device_heartbeats; +-------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |…
I have a site that gets a lot of traffic this time of year (around 50,000 users a day). The traffic goes up every year and all the sites on my server are starting to crash more, apparently because of…
Trying to create a conditional unique index in postgresql but unable to do so and getting this error Query 1 ERROR: ERROR: cannot use subquery in index predicate LINE 3: WHERE ( ^ this is how my query looks like…
I've created a column of usernames but the ids for each row is linked to another table. If a user is deleted, I'd like to change the username to 'Deleted'. So I'd like to allow for unique values except for…
I have faced this scenario in one of my interview. There will one table with millions of records and the table is going to have only two columns id which is primary key and time_stamp which is of type date…