query took too much time to load

Question

 ( SELECT 'no' AS aff,
         ss.orderid, ss.secondaff, ss.saletype, ss.price,
         ss.affiliate, ss.vendor,
         ss.cpa,ss.rebill,ss.salests,ss.fname,ss.lname 
    FROM sales ss 
   WHERE 1=1
     AND ( ss.vendor='3kpertrade' OR ss.affiliate='3kpertrade') 
ORDER BY ss.salests DESC ) 
  UNION ALL 
 (   SELECT 'yes' AS aff, ss.orderid,ss.secondaff,ss.saletype,sf.price,
            ss.affiliate,ss.vendor,ss.cpa,ss.rebill,ss.salests,ss.fname,ss.lname 
       FROM salesaff sf 
      INNER JOIN sales ss ON ss.orderid=sf.orderid 
      WHERE 1=1 
        AND ( ss.vendor='3kpertrade' OR ss.affiliate='3kpertrade') 
   ORDER BY sf.salests DESC ) 
ORDER BY salests DESC

I need to select data from sales table with given condition, and select data from salesaff table too if vendor=affiliate in sales table and ss.orderid=sf=orderid in above sql.

I wrote this with some help but query took 195 sec to load. sales table contains 8000 rows and index by salests,vendor,affiliate,orderid. The salesaff table contains 6000 rows and is indexed by orderid

Both table contains same columns.

Some facts: -> If i remove custom column from both ('yes' as aff, 'no' as aff), then query will execute if i use limit 100 or something. Otherwise it takes 195 sec to load.

If i use limit 100, then query is executed in 2 sec. But without limiting, it gives error code 500. Moreover, I need total sum of price too. Can anybody help me?

Answer 1

Heh heh. This is a hard one. You're probably getting correct results, but your hosting provider is timing out your query, so you can't tell. These two tables don't seem very large to me.

First, increase the timeout time if you can, especially for debugging. It's difficult to ensure correctness if your inefficient queries don't finish. The most efficient query in the world is worthless if it's wrong. Ask your hosting provider support person for help.

Second, you will get a timeout if it takes too long for the query to complete and return the first row of the resultset. You might also get a timeout if your host language is slow to consume the entire resultset, so make sure you're doing that efficiently.

Third, let's break it down. You're looking up a particular set of sales records. You're doing an OR operation. Most likely that guarantees a full table scan, which isn't so good for performance.

You may as well skip the 1=1 in your WHERE clauses. That's usually put in there as a hack to make the construction of code by a host language a little simpler. It shouldn't hurt performance, but it won't help.

Making a couple of guesses: assuming ss.orderid is a unique (autoincrement) primary key field, you might try adding compound indexes -- covering indexes -- on

   (vendor, orderid)

and

   (affiliate,orderid) 

then using the following query to retrieve the list of orderid values you need to look up.

         SELECT orderid FROM sales WHERE vendor = '3kpertrade'
          UNION
         SELECT orderid FROM sales WHERE affiliate = '3kpertrade'

This subquery efficiently looks up the set of orderid values you need. The two covering indexes I suggested will improve performance pretty significantly. (Don't use UNION ALL here, use UNION, or you may get some duplicated data.)

Then you can use this query to get some of your order data -- the first part of your UNION ALL.

SELECT 'no' AS aff,
       ss.orderid, ss.secondaff, ss.saletype, ss.price,
       ss.affiliate, ss.vendor,
       ss.cpa,ss.rebill,ss.salests,ss.fname,ss.lname 
  FROM sales ss 
  JOIN (
         SELECT orderid FROM sales WHERE vendor = '3kpertrade'
          UNION
         SELECT orderid FROM sales WHERE affiliate = '3kpertrade'
       ) ids ON ss.orderid = ids.orderid 

I suggest you debug your app with just this much of your query. Make sure it's displaying the correct stuff.

You appear to be implementing a shadow-booking system in your query. That is, when a particular order appears in your salesaff table you're duplicating it in this query's result set. It looks like the only information you retrieve from salesaff is the price. Is that correct? Do affiliate sales show up more than once in your UNION ALL result set?

Along these same lines, second half of your query will look like this:

SELECT 'yes' AS aff, ss.orderid, ss.secondaff, ss.saletype,
        sf.price,
        ss.affiliate, ss.vendor, ss.cpa, ss.rebill, ss.salests, ss.fname, ss.lname 
  FROM sales AS ss 
  JOIN (
         SELECT orderid FROM sales WHERE vendor = '3kpertrade'
          UNION
         SELECT orderid FROM sales WHERE affiliate = '3kpertrade'
       ) ids ON ss.orderid = ids.orderid 
  JOIN salesaff sf ON ss.orderid=sf.orderid 

If the only information you're pulling from salesaff is in fact the price, then a covering index

 (orderid, price)

will help this query a bit.

Finally, do the UNION ALL and the ORDER BY.

An observation. If sales.salests is a timestamp, and if sales.orderid is an auto-incrementing index, and if you never update sales.salests but only insert it, then ORDER BY orderid DESC will do the same thing as ORDER BY salests DESC. That might save a little time.

Answer 2

You could probably completely simplify by going through the table once and doing a left-join to the salesaff table. Then case/when for your yes/no status

I would have an index on ( vendor, aaffiliate, salests )

SELECT 
      CASE when sf.orderid is null 
           then 'no' else 'yes' end AS aff,
      ss.orderid, 
      ss.secondaff, 
      ss.saletype, 
      ss.price,
      ss.affiliate, 
      ss.vendor,
      ss.cpa,
      ss.rebill,
      ss.salests,
      ss.fname,
      ss.lname 
   FROM 
      sales ss
         LEFT JOIN salesaff sf 
            ON ss.orderid=sf.orderid 
   WHERE
         ss.vendor = '3kpertrade' 
      OR ss.affiliate = '3kpertrade'
   ORDER BY 
      ss.salests DESC

Answer 3

The first efficiency is that the order by in the subqueries is not necessary, unless you are putting limit clauses there. So try this:

(SELECT 'no' AS aff, ss.orderid, ss.secondaff, ss.saletype, ss.price, ss.affiliate, 
         ss.vendor, ss. cpa, ss.rebill, ss.salests, ss.fname, ss.lname 
 FROM sales ss 
 WHERE 1=1 AND ( ss.vendor='3kpertrade' OR ss.affiliate='3kpertrade')
) 
UNION ALL 
( SELECT 'yes' AS aff, ss.orderid, ss.secondaff, ss.saletype, sf.price, ss.affiliate, 
         ss.vendor, ss.cpa, ss.rebill, ss.salests, ss.fname, ss.lname 
 FROM salesaff sf INNER JOIN
      sales ss
      ON ss.orderid = sf.orderid 
 WHERE 1=1 AND ( ss.vendor='3kpertrade' OR ss.affiliate='3kpertrade') 
 ) 
ORDER BY salests DESC