Why is a specific where clause being added to this MySQL query for a performance killing bottleneck?

The problem is not adding a criterion; it knocks back the one that deals damage. In your original request, you had:

AND a.id = 965

      

        
        
        
      

    

This means that the execution of the query does not need to read the entire table a

( country

). In the second performance-driven query, you change this criterion to:

AND b.country = "whatever"
AND timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY)

      

        
        
        
      

    

You no longer have a limiting criterion a

, so everything works much slower.

Things get more complicated when you realize that b

this is another link to country

. However, going from condition to a

to b

(where it b

is on the outside of the outer join) is not trivial; it takes much longer to fulfill the query conditions.

Does this mean, because I'm not looking for a specific ID, I'm out of luck?

Given the structure of the request, the answer seems to be "yes", but the given structure of the request may be, shall we say, suboptimal.

Your query "is fast enough when working with one ID":

SELECT a.id,
       c.name,
       c.last,
       a.country,
       a.timestamp,
       b.timestamp AS o_timestamp
FROM   country a
       INNER JOIN user_info c
         ON ( a.id = c.id )
       LEFT JOIN country AS b
         ON ( a.id = b.id
              AND a.timestamp != b.timestamp
              AND a.country != b.country )
WHERE  b.timestamp = (SELECT c.timestamp
                      FROM   country c
                      WHERE  a.id = c.id
                             AND a.timestamp > c.timestamp
                      ORDER  BY c.timestamp DESC
                      LIMIT  1) 
       AND a.id = 965

      

        
        
        
      

    

I don't fully understand this request and what it is trying to do. You should be aware that outer joins are more expensive than inner joins and the conditions for the outer table, for example

b.timestamp = (...correlated sub-query...)

      

        
        
        
      

    

cruelly expensive. One problem is that the columns b

can be NULL inclusive timestamp

, but the subquery is wasted on that because the condition will not be met if the values ​​are not null, so we end up wondering "why is the join OUTER?"

When you added the revised condition, you should have received an ambiguous column name error as this timestamp can be from a

or c

. Also, the condition b.country = "whatever"

is different, which only makes sense when the values ​​are b

not zero, so again the OUTER join is questionable.

As I understand it, the table country

contains records of who entered which country and when. Also, FWIW, I'm pretty sure table joins user_info

are a minor performance issue; the problem boils down to three table references country

.

Judging by some explanations, you can build up the query gradually, maybe something like this.

• Find each pair of country records for the same id
  
  , where the records are contiguous in time sequence and the older of the pair is for the given country ("Jamaican Applicant") and the new one is for another country.

  The easy part of this:

  SELECT a.id, a.country, a.timestamp, b.country, b.timestamp
    FROM country AS a
    JOIN country AS b
      ON a.id = b.id
     AND b.timestamp > a.timestamp
     AND a.country = 'Jamaica Applicant'
     AND b.country != a.country
  
        
  
          
          
          
        
  
      

  This does most of the work, but does not provide contiguity for the records. To do this, we must insist that country
  
  there is no record in the table in the same id
  
  between (but not including) the two timestamps a.timestamp
  
  and b.timestamp
  
  . This additional condition DOES NOT EXIST:

  SELECT a.id,
         a.country   AS o_country,
         a.timestamp AS o_timestamp,
         b.country   AS n_country,
         b.timestamp AS n_timestamp
    FROM country AS a
    JOIN country AS b
      ON a.id = b.id
     AND b.timestamp > a.timestamp
     AND a.country = 'Jamaica Applicant'
     AND b.country != a.country
   WHERE NOT EXISTS
         (SELECT *
            FROM country AS c
           WHERE c.timestamp > a.timestamp
             AND c.timestamp < b.timestamp
             AND c.id = a.id
         )
  
        
  
          
          
          
        
  
      

  Please note that BETWEEN AND notation is not appropriate. It includes endpoints in the range, but we clearly need excluded endpoints.

• Given the list of country entries above, we only need to select those rows where ... hmmm, well, what's the criterion? I think you can choose, but the result can be easily joined to a table user_info
  
  :

  SELECT e.id, u.name, u.last, e.o_country, e.o_timestamp, e.n_country, e_n_timestamp
    FROM (SELECT a.id,
                 a.country   AS o_country,
                 a.timestamp AS o_timestamp,
                 b.country   AS n_country,
                 b.timestamp AS n_timestamp
            FROM country AS a
            JOIN country AS b
              ON a.id = b.id
             AND b.timestamp > a.timestamp
             AND a.country = 'Jamaica Applicant'
             AND b.country != a.country
           WHERE NOT EXISTS
                 (SELECT *
                    FROM country AS c
                   WHERE c.timestamp > a.timestamp
                     AND c.timestamp < b.timestamp
                     AND c.id = a.id
                 )
         ) AS e
    JOIN user_info AS u ON e.id = u.id
   WHERE e.o_timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY);
  
        
  
          
          
          
        
  
      

I am not going to guarantee that the performance will be better (or even that it is syntactically correct, it has not gone through the SQL DBMS). But I think the complex query structure for getting adjacent dates is neater and probably better than the original code. Please note in particular that it does not use any external constraints, (explicit) ordering or constraints. This should help.