Skip to content
The Chat Archive
gpt-4o-2024-05-13· OpenAI WildChat-4.8M (AI2)

Full scan on table offer, how to rewrite query for fix it? SELECT c.orderid FROM…

  1. 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);

  2. Assistant

    Optimizing SQL queries, particularly to avoid full table scans, can be a challenge. Here are a few general recommendations:

    1. Indexes: Ensure that there are appropriate indexes on the columns used in your joins and WHERE conditions.
    2. Selectivity of Columns: Order your WHERE conditions based on selectivity. Most selective conditions should come first.
    3. 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:

    1. 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)
    2. affiliate Table:

      • Index on id
      • Index on has_offers_id
    3. offer Table:

      • Index on id
      • Index on has_offers_id
    4. tariff Table:

      • Index on id
      • Index on is_conversion_concealing

    You can create the indexes similar to the following example for the conversion_reward table:

    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, and tariff tables.

    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.