0

I am working with a existing database's table from another application. I want to query newly inserted records in a fixed interval. Normally, in a table with AUTO INCREMENT id, I can store the last fetched id and use it in the query like WHERE id > :last_id. However, this table doesn't use AUTO INCREMENT id but use uuid as primary key. So is there any way to fetch new records only?

This DB is using MySQL. I can't change the database structure. The data size is quite huge so I don't think passing fetched uuids in query like WHERE uuid NOT IN (:fetch_uuids) will be a viable solution.

Edit: There is created field, but unfortunately there is no warranty that the records with smaller created will be inserted first. So there is the risk of missing records using it. The data were inserted by other application, and I only have read permission in this database.

eee
  • 280
  • 4
  • 15
  • Doesn't your table have a `created` column or similar i.e. a timestamp when the record was created. Alternatively could you create a new separate table with auto increment ids and/or record creation date linked to this table? – vogomatix May 10 '18 at 04:00
  • Do you have any updated/created date fields or anything that could help identify latest record? – danish May 10 '18 at 04:03
  • More precisely I can't change the database structure (only have read permission). There is `created` field, but unfortunately there is no warranty that the records with smaller `created` will be inserted first. So there is the risk of missing records using `created` field. – eee May 10 '18 at 04:11
  • 1
    Sounds like a strange use of a `created` field! – Nick May 10 '18 at 04:14
  • Yes I saw the a record with earlier `created` was inserted few seconds after a later one. I guess it came from the lag time between application servers or long execution time. – eee May 10 '18 at 04:31
  • 1
    Ah, they should be using `default current_timestamp` instead. – Nick May 10 '18 at 04:32
  • 1
    Rows have no innate (humanly discernible) order, other than that established by an AI id or a timestamp – Strawberry May 10 '18 at 06:04

1 Answers1

0

Your question doesn't state whether there is a column containing the creation time of the record. If this exists then you could use this.

You have stated you cannot change the table structure, but are you sure you cannot add columns onto the existing structure? Your problem could be solved by adding an auto-increment 'secondary' ID and/or record creation timestamp. If you cannot modify the existing tables, could you perhaps create a new table with this additional information?

A solution to your problem may be in this answer. You may be able to either add an additional column to the existing table, or alternatively insert ids into a new table where you create an ID based on a TRIGGER from the original table

vogomatix
  • 4,856
  • 2
  • 23
  • 46
  • I updated the question to add some missing information. I hope MySql has some information like physical insertion order that we can use in `id` stead. Not likely to happen though. – eee May 10 '18 at 04:26
  • Some time since you last posted but I have added an idea at the end of my 'answer' – vogomatix May 21 '18 at 15:30
  • Thanks for your suggestion. But it's not possible in this case since I only have read permission in this database. – eee May 24 '18 at 15:15