Full scan on table offer, how to rewrite query for fix it? SELECT c.orderid FROM…
- User
Full scan on table offer, how to rewrite query for fix it?
SELECT c.order_id FROM conversion_reward AS c INNER JOIN affiliate AS a ON c.affiliate_id = a.id INNER JOIN offer AS o ON c.offer_id = o.id LEFT JOIN tariff AS t ON c.tariff_id = t.id WHERE (((((((c.status = 2) AND (c.conversion_external_status = 1)) AND ((c.order_id IS NOT NULL))) AND (a.has_offers_id = 1445)) AND (o.has_offers_id = 1064)) AND (c.date >= date '2024-07-08')) AND (c.date <= date '2024-08-01')) AND (t.id IS NOT NULL AND t.is_conversion_concealing);
- Assistant
Optimizing SQL queries, particularly to avoid full table scans, can be a challenge. Here are a few general recommendations:
- Indexes: Ensure that there are appropriate indexes on the columns used in your joins and
WHEREconditions. - Selectivity of Columns: Order your
WHEREconditions based on selectivity. Most selective conditions should come first. - Use of Date Functions: Ensure no functions are applied to the column values that would prevent the use of indexes.
Here is a revised version of your query with some of these considerations. I also corrected a potential syntax issue with date literals and reorganized the query for better readability.
SELECT c.order_id FROM conversion_reward AS c INNER JOIN affiliate AS a ON c.affiliate_id = a.id INNER JOIN offer AS o ON c.offer_id = o.id LEFT JOIN tariff AS t ON c.tariff_id = t.id WHERE c.status = 2 AND c.conversion_external_status = 1 AND c.order_id IS NOT NULL AND a.has_offers_id = 1445 AND o.has_offers_id = 1064 AND c.date >= DATE '2024-07-08' AND c.date <= DATE '2024-08-01' AND (t.id IS NOT NULL AND t.is_conversion_concealing);Recommendations for indexing:
-
conversion_reward Table:
- Index on
status - Index on
conversion_external_status - Index on
order_id - Index on
date - Composite index on (
affiliate_id,offer_id,tariff_id)
- Index on
-
affiliate Table:
- Index on
id - Index on
has_offers_id
- Index on
-
offer Table:
- Index on
id - Index on
has_offers_id
- Index on
-
tariff Table:
- Index on
id - Index on
is_conversion_concealing
- Index on
You can create the indexes similar to the following example for the
conversion_rewardtable:CREATE INDEX idx_conversion_reward_status ON conversion_reward(status); CREATE INDEX idx_conversion_reward_conversion_external_status ON conversion_reward(conversion_external_status); CREATE INDEX idx_conversion_reward_order_id ON conversion_reward(order_id); CREATE INDEX idx_conversion_reward_date ON conversion_reward(date); CREATE INDEX idx_conversion_reward_affiliate_offer_tariff ON conversion_reward(affiliate_id, offer_id, tariff_id);Similarly, create indexes for
affiliate,offer, andtarifftables.For more detailed indexing strategies, take note of your specific database system (MySQL, PostgreSQL, Oracle, etc.) and analyze the query execution plan using tools provided by your database system.
- Indexes: Ensure that there are appropriate indexes on the columns used in your joins and