Questions tagged [full-table-scan]
61 questions
1
vote
1 answer
Composite index is ignored when selecting unindexed columns (Oracle)
We have a simple join statement in which some of the when clauses may turn into is null. The statement is generated by an application.
A problem with a query plan arises when we have this is null constraint. We followed the approach described in…
Zaplatki
- 35
- 7
1
vote
1 answer
How to avoid full table scan when update with IN clause in MySQL
I got 2 MySQL tables: termlist and blacklist. They both have index on field 'term' and blacklist has another index on field 'status'.
I want to update status of terms in termlist, that also appear in blacklist with status as 'A', to 'B', I issue…
Scott Chu
- 972
- 14
- 26
1
vote
2 answers
Using index to speed up child <> parent query
I have query similar to this:
select *
from table1
where status = 'ACTV'
and child_id <> parent_id
The problem is that this table is quite and large and Oracle is doing full table scan.
I was trying to create an index (with status, child_id,…
Paweł P
- 33
- 3
1
vote
2 answers
Innodb, clustered indexes, and slow_query_log - hurt by the primary key?
In the last few months we've migrated a few tables from MYiSAM to InnoDB. We did this, in theory, for the row locking advantage, since we are updating individual rows, through multiple web-scraping instances. I now have tens of thousands of…
user3242558
- 105
- 1
- 9
1
vote
3 answers
Optimize date query for large child tables: GiST or GIN?
Problem
72 child tables, each having a year index and a station index, are defined as follows:
CREATE TABLE climate.measurement_12_013
(
-- Inherited from table climate.measurement_12_013: id bigint NOT NULL DEFAULT…
Dave Jarvis
- 30,436
- 41
- 178
- 315
1
vote
1 answer
Retrieving latest datetime for each product
I have a SQLite3 DB which is about 3GB in size, I am trying to query it in order to bring up the latest order for a specific product between 2 dates.
Here is the query used to create the table:
CREATE TABLE "ProductOrders" (
"ID" INTEGER NOT…
user3221393
- 31
- 3
1
vote
1 answer
Incremental update of millions of records, indexed vs. join
I'm currently developing a strategy for an incremental update of our user data. We assume 100_000_000 records in our database of which approximately 1_000_000 records are updated per workflow.
The idea is to update records in a MapReduce job. Is it…
Philipp Pahl
- 211
- 1
- 2
- 6
0
votes
1 answer
C# DynamoDBv2 scan with OR operator
How do I scan a DynamoDB table in C# returning records matching any of the 2 columns provided in a ScanFilter - OR operator? For example, column "a" == value OR "b" == value. Is this supported by the API? Currently I am scanning the table twice for…
Kok How Teh
- 3,298
- 6
- 47
- 85
0
votes
1 answer
How to avoid full table scan in mysql query with large output
I have really big table (over 10 million rows) and query which returns a lot of data. I need to make it to run faster. So I tried to add covering indexes (which consisted with values from where clause and id) but there still was full table scan even…
Olha Tkachuk
- 17
- 3
0
votes
1 answer
How to avoid full table scan in mysql join query
Consider the following query:
SELECT
`banner`.`id`,
`region`.*
FROM
`nms_section_region_banner` AS `section`
JOIN `aw_rbslider_slide_region` AS `region`
ON
FIND_IN_SET(
region.region_id,
…
Sardharmohamed M
- 1
- 1
0
votes
1 answer
Full table scan behaviour with cache and nocache in oracle 12c
I have a same query running on two different DB servers with almost identical config. Query is doing Full Table scan(FTS) on one table
SELECT COUNT (1) FROM tax_proposal_dtl WHERE tax_proposal_no = :b1 AND taxid != :b2 AND INSTR(:b3 , ',' || …
Atif
- 2,011
- 9
- 23
0
votes
1 answer
How to avoid table scan on SQL Server in this situation
There are two tables, Costs and Logs. The data in Costs table can be in the millions of rows, and in Logs table it can be billions of rows.
I need to update the CostBy column in Costs table in a service task in production environment within 100…
Jimmy
- 45
- 1
- 7
0
votes
1 answer
MySQL Query full table scan when keys available
In attempting to pull a large series of columns (~15-20) from several joined tables, I put together 2 views that would pull the necessary information. In my local DB (only ~1k posts rows), joining these views worked fine, however; when I created…
Hugo Pakula
- 385
- 2
- 4
- 17
0
votes
1 answer
sql (mysql) optimize query oder schema, avoiding full table scan
I have one table with data. The table has entries for orders, each order has some types (or state e.g ordered, ..., polished, packed, shipped).
Now, I want to do this query.
select * from orders as o
where not exists
(SELECT * from orders as…
hans.g
- 1
0
votes
2 answers
How to improve the performance of table scans with innodb
Brief: Is there any way to improve the performance of table scans on InnoDB tables?
Please, do not suggest adding indexes to avoid table scans. (see below)
innodb_buffer_pool_size sits at 75% of server memory (48 GB/64GB)
I'm using the latest…
Carl
- 1
- 2