MariaDB – Get Rows Changed Between Two Dates

limitsmariadborder-by

I have a table _sync_history with this data:

+----+----------------+---------------------+
| id | object_name    | sync_time           |
+----+----------------+---------------------+
|  1 | territory      | 2015-07-21 22:29:02 |
|  2 | user           | 2015-07-21 22:29:19 |
|  3 | userterritory  | 2015-07-21 22:29:38 |
|  4 | account        | 2015-07-21 22:30:05 |
|  5 | accountshare   | 2015-07-21 22:39:04 |
|  6 | address_vod__c | 2015-07-22 00:47:38 |
|  7 | group          | 2015-07-22 01:23:30 |
|  8 | recordtype     | 2015-07-22 01:23:51 |
|  9 | user           | 2015-07-23 13:04:20 |
| 10 | account        | 2015-07-23 13:04:59 |
+----+----------------+---------------------+

I want to get the rows on user between 2015-07-21 22:29:19 and 2015-07-23 13:04:20 meaning the rows between last sync_time and previous sync_time for a given object_name. I was doing this:

SELECT * FROM `user` WHERE lastmodifieddate > (SELECT sync_time FROM `_sync_history` WHERE object_name='user' ORDER BY sync_time ASC LIMIT 1)

But that will give me just the ones from last sync_time and above which is not right, how do I get the ones I need?

Best Answer

You need to find both times, one using what you have (LIMIT 1) and the second using LIMIT 1 OFFSET 1.

I'd also use ORDER BY ... DESC, not ASC. Your wording suggests you want the rows between the last 2 appearnces of 'user':

SELECT * 
FROM user 
WHERE lastmodifieddate > 
        (SELECT sync_time 
         FROM _sync_history 
         WHERE object_name = 'user' 
         ORDER BY sync_time DESC 
         LIMIT 1 OFFSET 1
        )
  AND lastmodifieddate <
        (SELECT sync_time 
         FROM _sync_history 
         WHERE object_name = 'user' 
         ORDER BY sync_time DESC 
         LIMIT 1 OFFSET 0
        ) ;

Related Solutions

MySQL: ORDER BY useless

I guess this depends on the storage-engine. I can tell for MyISAM, a select without any order will return rows sequentially as they occur in file (even if held in memory).

As you ordered data during write, it will be ordered by id within MyISAM-temptables. So you can safely assume it will always return correct results as long as you don't delete from myISAM-temp.

But the new question is - is temptable created in myIsam-format? I assume the default storage engine will be used. This used to be MyISAM, but was changed to InnoDB after MySQL 5.1.

I don't know how innodb would handle an unsorted select.

-- edit

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

In big (on disk) temporary tables myISAM is used. It is not intentional behavior rows are served sequentially in order as written into, but it is by design (as documented) reliable as long as myISAM table is appended only.

This is most probably not true for any "hashed table" like InnoDB, BDB, and eventually MEMORY. But when memory table is converted to "on disk table", current order is "frozen on the disk", and therefore reliable again.

But I guess we have an other kind of problem here:

The 'real' problem seems to be your "order by" feels too slow, and you would like to get around that. Do you by chance know your table size and sessions max_heap_table_size? This give you a hint if your table has been converted to myisam. You can also play with Index-Hash-method (BTREE, HASH) to look for optimization.
Are you aware that LIMIT N,M will get painfully slow because it has to select N rows, discard them, and then select M rows to return?

Mysql – Extract data for all groups in a single SELECT statement

This works, provided the combination groupid - startdate is unique:

SELECT s1.groupid, s1.id FROM schedule s1, (
  SELECT groupid, MAX(startdate) startdate
  FROM schedule
  WHERE @date >= startdate
  GROUP BY groupid
) s2
WHERE s1.groupid = s2.groupid
AND s1.startdate = s2.startdate

Related Question