From time to time I encounter a strange MySQL behavior. Let's assume I have indexes (type, rel, created), (type), (rel). The best choice for a query like this one:
SELECT id FROM tbl
WHERE rel = 3 AND type = 3
ORDER BY created;
would be to use…
I am currently learning indexes in Oracle.
In Oracle documentation, following can be found :
Although the database creates an index for you on a column with an integrity constraint, explicitly creating an index on such a column is…
I am looking to compare indexes and further optimize my code. What I would like to do is force the query to run without an index so I can see what difference it has made. Is it possible to do this?
I have a simple count query that can use Index Only Scan, but it still take so long in PostgresQL!
I have a cars table with 2 columns type bigint and active boolean, I also have a multi-column index on those columns
CREATE TABLE cars
(
id BIGSERIAL…
I've come across a situation where my database operations on many of my SQL Server database's tables have become very slow as more records have been added (5s for single insert to a table with 1 million records).
I've estimated that this may be due…
I access several tables remotely via DB Link. They are very normalized and the data in each is effective-dated. Of the millions of records in each table, only a subset of ~50k are current records.
The tables are internally managed by a commercial…
I'm trying to understand Cassandra's storage engine when it comes to composite columns. Unfortunately, the documentation I've read so far contains errors and is leaving me a bit blank.
First, terminology.
Composite columns comprise fully…
delayed_job does a query like this regularly:
SELECT "delayed_jobs".*
FROM "delayed_jobs"
WHERE ((run_at <= '2012-05-23 15:16:43.180810' AND (locked_at IS NULL OR locked_at < '2012-05-23 11:16:43.180841') OR locked_by = 'host:foo pid:1') AND…
I have a SELECT statement which I would like to optimize. The mysql - order by optimization says that in some cases the index cannot be used to optimize the ORDER BY. Specifically the point:
You use ORDER BY on nonconsecutive parts of a key
…
So as I was following the Ruby on Rails Tutorial by Michael Hartl I noticed that in the users table we added a unique index for the :email attribute to improve the efficiency of the find method so it doesn't search row by row. So far we have been…
The problem is I keep getting seq scan on a rather simple query for a very trivial setup. What am I doing wrong?
Postgres 11 on Windows Server 2016
Config changes done: constraint_exclusion = partition
A single table partitioned to 200 subtables,…
So I made a backup of a table using pg_dump:
pg_dump -U bob -F c -d commerce -t orders > orders.dump
This table had several listed indexes such as a primary key
However when I restore this table into a development database on another system using…
Is there any way to create indexes in oracle only if they don't exists ?
Something like
CREATE INDEX IF NOT EXISTS ord_customer_ix
ON orders (customer_id);
Is there going to be any real benefit to me putting indexes onto date fields that are going to be mainly used in queries using stuff like.
dateField < 'var'
And
'var' BETWEEN dateField1 AND dateField2
The searches get done a lot but I am never…
When creating a model with a models.varchar(...) field, a varchar_pattern_ops index is being created.
This is the table generated in postgresql
Table "public.logger_btilog"
Column | Type | Modifiers…
