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…
I'm having trouble getting SQLite to index an expression. Specifically, I want to create an index on a particular property in a JSON object.
CREATE TABLE test (tags JSON);
INSERT INTO test VALUES(JSON_OBJECT('someKey', 'someValue'));
CREATE INDEX…
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,…
I have created a trigram index in order do execute a query with a 'like %text%' condition, but PostgreSQL 9.6 doesn't use the index to perform the query.
CREATE EXTENSION pg_trgm;
CREATE INDEX tb_estabelecimento_index08
ON tb_estabelecimento
…
I created a new index on a table with 35million records and its been running for nearly 1 day now. Previously when I created indexes it took 20 minutes, there columns were however floats. The new idnex is on a varchar(45)
I used the processlist…
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…
Is a unique constraint an index by default? If not, does a unique constraint has the same performance results as an indexed column when using it in the SELECT ... WHERE clause?
Thanks
I've unsuccessfully been through the AWS forum and Stack Overflow trying to find a solution to the following error:
Index column size too large. The maximum column size is 767 bytes
I am running a WordPress website with 1.5M records in the postmeta…
Is there some tool that does this? You give it a SQL query and it gives suggestions. For example, I'm trying to optimize the following query:
Load (383.2ms) SELECT COUNT(*) as plays, p.chosen_race as race, p.won as won, r.game_type as type FROM…
indexes make read fast but write slower. But why can't you have single writes and have db add indexes asynchronously with time, also cache in the INSERT until it's indexed?
Is there any database like that?
In a postgres database, I have a unique constraint and two unique indexes created for the same. I deleted the constraint using the query below:
alter table my_schema.users drop constraint users_dept_uk
It has dropped the constraint and one index…
I use PostgreSQL 11.1 and I'm trying to gather information from pg_stat_all_indexes table about indexes usage to determine whether a particular index can be removed or not - according to Index size/usage statistics section in…
I have this query to get the list of indexes on a table:
SELECT
ns.nspname as schema_name,
tab.relname as table_name,
cls.relname as index_name,
am.amname as index_type,
idx.indisprimary as is_primary,
idx.indisunique as…
The data structure used for indexing in a DB table is B-Tree (default, out of B-Tree, R-Tree, Hash). Since look-ups, deletions, and insertions can all be done in logarithmic time in a B-Tree, then why is only reading from an indexed table is faster…
