Most voted "índices" questions
Indexing data structures is a general technique to improve the speed of data searches. A database index contains keys built from one or more columns in the table or view, and pointers that map to the storage location of the specified data.
Learn more…111 questions
Sort by count of
-
48
votes0
answers1178
viewsWhy are cost estimates in Postgresql so wrong?
Could someone help me understand why Postgresql missed several cost estimates in an experiment I did. I’m doing an experiment with 22 darlings of Benchmark TPCH[1], to verify index performance in…
-
36
votes2
answers25876
viewsWhat are the advantages and disadvantages of using indexes in databases?
What are the advantages and disadvantages of using indexes in databases?
-
28
votes4
answers1189
viewsHow does a search engine work?
I am making it clear that I am not asking how to make my site appear better in the search index. Today we have several search engines. Some very well known as Google and Bing and others not so much,…
-
24
votes1
answer2772
viewsAre unnecessary bank ratings a problem?
Indexes are usually added to improve the performance of queries. However, it is not usually recommended to add indexes in all columns, only where the need is identified. Why this recommendation?…
-
23
votes3
answers1636
viewsHow to create a conditional index in Mysql?
How to create an index filtered by a specific data range? As far as I know, it is impossible to do this directly in Mysql. In some other database systems, there is usually a WHERE clause for this…
-
23
votes1
answer2914
viewsIndexes in Mysql queries
Using indexes in mysql queries really makes the result come quickly for some large queries or tables. For example in the query below: SELECT * FROM table WHERE status = 1; For a table with 1 million…
-
19
votes1
answer11373
viewsHow to use a specific index in a SQL Server query?
I have a non-standard table in SQL Server installed on my PC which is fed monthly by Integration Services (SSIS) from a report in an Excel spreadsheet. I mean, I’m keeping track of a certain report.…
-
16
votes2
answers673
viewsWhen and in which columns should indexes be used?
Reading an article that has nothing to do with database I came across information that use of indexes can bring great improvements to database performance. I have two questions about that: When…
-
15
votes3
answers2752
viewsWhat are indexes in SQL Server for?
For what the index serves, I know it improves performance, but what the database does behind that improves this performance. When is it recommended to use? And where should I use an index?
-
15
votes3
answers14534
viewsWhat is the INDEX index in Mysql for?
What is the content for INDEX in Mysql? ALTER TABLE `tabela` ADD INDEX `tabela_id_index` (`tabela_id` ASC);
-
15
votes1
answer1791
viewsHow to apply Dexes to improve the performance of the queries?
I’m looking to increase the performance of my database, which has over 1 million records. Research I have undertaken Sobre index link1 Cluster-wise link1 Link2 Doubt The idea of cluster it seemed…
-
15
votes4
answers2051
viewsDropping indexes or Foreign Keys in a table can slow the query?
What I always read some tutorials talking about Mysql and I’ve also heard from some fellow programmers is that the lack of indexes or foreign keys in a table can make a query slow. For example, I…
-
13
votes2
answers722
viewsHow index selectivity works
Reading about indexes I recently came across the term "selectivity". Some places said more selective fields should come first on the index, others say no or that it depends. I have the following…
-
12
votes3
answers1569
viewsHow to create multiple entries in an index based on columns in the same row?
I have never found a good way to index multiple columns of a row as index entries or simulate this feature in Mysql. The problem arises when you have fields working as tags or a similar concept.…
-
8
votes1
answer13268
viewsHow to create a JOB on Oracle?
I have the command below to perform the analysis of Oracle indexes: EXEC DBMS_STATS.GATHER_SCHEMA_STATS('USUARIO', estimate_percent=> 25); I would like to know how to schedule this command so…
-
8
votes2
answers654
viewsPerformance of query LIKE in Mysql
Is there any way to increase the performance of a query with LIKE '%string%' in Mysql? I know that if the LIKE for 'string%', is faster. The problem is when the % is at the beginning of the string.…
-
8
votes1
answer564
viewsCardinality of the index does not update
I am creating a program that connects to a Mysql database online, and when starting it it creates a table with an index if it does not exist. To check if everything is ok I am analyzing the database…
-
8
votes2
answers540
viewsHow does an index improve the performance of a query?
Why does creating an index improve the performance of a query? Is any change made to the table structure? Is the data structure for disk storage modified? Is the algorithm used to perform the search…
-
8
votes1
answer4263
viewsWhat is the difference between ASC or DESC in clustered indices?
When I’m going to create an index no clustered in SQL SERVER, appears in SSMS the option to choose if the option "Columns" is ASC or DESC, as shown below: It is usually used ASC or DESC for sorting…
-
7
votes2
answers511
viewsFilters or Sort, which should be the first when creating an index in the database
Given, for example, the following Query: SELECT ClienteId, Nome, DataNascimento, Cidade, Estado, DataCadastro FROM Cliente WHERE Estado = :Estado AND Cidade = :Cidade ORDER BY DataCadastro,…
-
7
votes1
answer5885
viewsWhat are INDEX, B-Tree, hash, Gist, and GIN?
In the the Postgresql Manual has the following excerpt : Postgresql provides the index methods B-Tree, hash, Gist, and GIN. Postgresql provides the B-Tree, hash, Gist and GIN index methods. But…
-
7
votes2
answers223
viewsIn SQL queries should I follow the order of the index?
If in my table X an index is created with the fields A, B and C (in this order), in the SQL queries I must follow exactly this order? Example following the order of the index: SELECT * FROM X WHERE…
-
7
votes1
answer151
viewsWhat are index hints?
I saw a comment from @Ricardopunctual that mentioned Density-based index hints your question seems very confusing. it starts with a very basic thing such as "how do I process in sql server", which…
-
7
votes2
answers195
viewsQuestion about entering items in a list with indexes inside Python brackets
You can insert items into a list using methods such as, append(), extend(), or insert(). But it is also possible to insert through indexes in square brackets, normally used to replace items and not…
-
6
votes2
answers934
viewsHow to create an index file using B+ tree
I have a B+ tree that acts as the index of a data file. This index must be saved in an index file. A struct node or node of the B+ tree is: typedef struct node { void ** pointers; int * keys; struct…
-
6
votes1
answer382
viewsWhat is the difference between reverse list (Inverted index) and reverse list (Reverse index)?
what concept, characteristics, advantages and disadvantages and an example of use (application).
-
6
votes1
answer3395
viewsError creating table with INDEX VISIBLE in Workbench
I created a diagram of a BD using Mysql Workbench and had it generated. But the following error happens when I run the query: Executing SQL script in server ERROR: Error 1064: You have an error in…
-
5
votes5
answers5699
viewsHow to get the current iteration index of a foreach?
Usually in order to get the current iteration index within a foreach, I do it this way: int i=0; foreach (var elemento in list) { // Qualquer coisa i++; } I believe there are other methods to get…
-
5
votes1
answer217
viewsBetter BD structure with large number of data and filterable columns
I have a Mysql database with a table that is taking too long to complete a query. I would like to know what would be the best database structure indicated for a table with many fields that are…
-
5
votes1
answer100
viewsAmount of index influence on performance?
I have a relatively large database. And the consultation in it varies a lot and I have some doubts regarding performance: 1- Create multiple indexes, taking into account the Where (E.condicao = '1')…
-
4
votes2
answers508
viewsWhen is it recommended to use decreasing indexes?
For default, relational databases create indices using increasingly ordered binary tree structures. But there is the possibility of creating it in a decreasing way too. My question is whether (and…
-
4
votes1
answer41
viewsIndex value not being recognized correctly
I have the following code jQuery: $('.dock-a').hide(); $('.dock').each(function(){ var DOCK = $(this); DOCK.click(function(){ var DOCK_largura = $(window).width(), DOCK_index = $(this).index(),…
-
4
votes3
answers1067
viewsIndex or key in Javascript object
I have this situation: const movies = { 1: { id: 1, name: 'Planet Earth', }, 2: { id: 2, name: 'Selma', }, 3: { id: 3, name: 'Million Dollar Baby', }, What would these numbers be 1, 2 and 3 with two…
-
4
votes1
answer1716
viewsHow to create a single index based on two columns?
I have a table with these columns: ip nome email I want you to ip and the email are a single pair. The same ip may contain several records and a single email may also. But the same ip and even email…
-
4
votes3
answers1476
viewsIs it necessary to create a primary key with auto increment?
When I create a table that already has a field with an index unique, that will be referenced by other tables, can I leave it as primary key? Or is it good practice to create another field, with auto…
-
4
votes1
answer1340
viewsWhat are the differences between the HASH and BTREE algorithms used in an index?
I realized that I can create two types of indexes for one determining field in Heidisql, which use the algorithm HASH or BTREE, see below: See the CREATE code from an example table for the…
-
3
votes2
answers2614
viewsHow to get the index creation date in SQL Server?
By SQL Server Management Studio I did not find in the properties of an index its creation date. How can I get the creation date of a specific index by just referring by name?
-
3
votes1
answer534
viewsIndex for MYSQL - ORDER BY influence when creating an index?
I have the following query: SELECT type,name,points FROM clients WHERE type='1' ORDER BY name I created an index for the "clients" table only above the "type field". Adding an ORDER BY on top of a…
-
3
votes1
answer114
viewsFind the index of all occurrences in a jquery string
How to find the index of all occurrences of a string within another string with jquery? Example: String all "three sad tigers for three wheat dishes, three wheat dishes for three sad tigers." String…
-
3
votes1
answer80
viewsFull Text Index for Database facing Ecommerce!
I read several posts here on SOPT about creating índices which left me with many doubts I had, but my main one is about FULL TEXT! In a database that is exclusively aimed at ecommerce, where there…
-
3
votes2
answers614
viewsIt takes time to count records the first time
I’m doing a stress test on an app I’m developing. I added a few million records to a table, which is what it will have in about 5 years of use. I use Entity Framework. By counting the records of a…
-
3
votes1
answer115
viewsWhy does bitmap indexing work well for low cardinality domains?
I have just read chapter 29 of the book Overview of Data Warehousing and OLAP On page 725 the author talks about indexing techniques to support high performance access, this technique is called…
-
3
votes0
answers47
viewsWhat are the cardinalities of the Indians?
Checking the Index using the following command: show indexes from tabela_exemplo; The column Cardinality left me in doubt. It holds an integer value for each index pointed to one or more columns…
-
3
votes1
answer296
viewsHow to create an index for two tables at once in SQL Server?
I’m pretty sure there’s no way but, come on... I have the following scenario: (I included only the fundamental fields for brevity) - Tabela Venda: - id: int; - cliente_id: int; - cliente_tipo:…
-
3
votes1
answer91
viewsHow to use indexes beyond the primary key?
I have a question about that. Consider a table with the following columns: id (chave primaria), cliente, cidade, estado I already understood that a consultation with condition WHERE id = X would…
-
2
votes2
answers220
viewsIs there a function to reindexe a multidimensional array?
Guys, in need of help, I have a function that returns the following array: Array ( [0] => Array ( [0] => valor1 [2] => valor2 ) [1] => Array ( [0] => valor3 [1] => valor4 [3] =>…
-
2
votes1
answer203
viewsIndexes in primary key columns
I know that each case is a case, and that there are no ready-made rules on the creation of indexes. In my case, considering that the primary key of each table of my bank is used in wheres of my bank…
-
2
votes1
answer156
viewsCan index in an ENUM field bring any advantage?
I was reading in a reply from Soen, that the fields of a table that have INDEX can optimize a query. Of course, as long as this query is known, such as a user survey by the email, for example. I…
-
2
votes0
answers72
viewsShould fields in the order by clause be indexed?
I’m reading a lot about using indexes in Mysql. I noted that it is really necessary to know how to use it at the right time and in the appropriate columns in order to make the queries faster and not…
-
2
votes1
answer639
viewsIs there a nomenclature standard for table index names in Mysql?
It seems simple this question, but wanted to know how to set a pattern. Usually using the Mysql Workbench a default is set automatically, as in the example below: fk_tabela1_tabela2_idx Note: I am…