which one is a faster/better sql practice?

Question

Suppose I have a 2 column table (id, flag) and id is sequential. I expect this table to contain a lot of records. I want to periodically select the first row not flagged and update it. Some of the records on the way may have already been flagged, so I want to skip them.

Does it make more sense if I store the last id I flagged and use it in my select statement, like

select * from mytable where id > my_last_id order by id asc limit 1

or simply get the first unflagged row, like:

select * from mytable where flagged = 'F' order by id asc limit 1

Thank you!

Answer 1

Option two is the only one that makes sense unless you know that you're always going to process records in sequence!

Answer 2

If you create an index on flagged, retrieving an unflagged row should be pretty much an instant operation. If you always update them sequentially, then the first method is fine though.

Answer 3

Assuming MySQL, this one:

SELECT  *
FROM    mytable
WHERE   flagged = 'F'
ORDER BY
        flagged ASC, id ASC
LIMIT 1

will be slightly less efficient in InnoDB and of same efficiency in MyISAM, if you have an index on (flagged, id).

InnoDB tables are clustered on the PRIMARY KEY, so fetching the first record in id does not require looking up the table.

In MyISAM, tables are heap-organized, so the index used to police the PRIMARY KEY is stored separately from the table.

Note the flagged in the ORDER BY clause may seem to be redundant, but it is required for MySQL to pick the correct index.

Also, the composite index should be on (flagged, id) even in InnoDB (which implicitly includes the PRIMARY KEY into each index).

Answer 4

You could use

Select Min(Id) as 'Id'
From dbo.myTable
Where Flagged='F'

Assuming the Flagged = 'F' means that it is not flagged.