Mysql sequential numbering based on MAX of another column
STEP 01 : Create index on date_submitted

ALTER TABLE lp_pictures ADD INDEX (date_submitted);


STEP 02 : Create separate table to order the id values

CREATE TABLE lp_picture_ordering
(
    id INT NOT NULL AUTO_INCREMENT,
    plate_uid varchar(128) NOT NULL,
    uid varchar(64) NOT NULL,
    PRIMARY KEY (plate_uid,id),
    UNIQUE KEY uid (uid)
) ENGINE=MyISAM;


STEP 03 : Load the table ordering info into the ordering table

INSERT INTO lp_picture_ordering (plate_uid,uid)
SELECT plate_uid,uid FROM lp_pictures ORDER BY date_submitted;


STEP 04 : Perform UPDATE JOIN to assign id values

UPDATE lp_pictures A
INNER JOIN lp_picture_ordering B
USING (uid) SET A.id = B.id;


STEP 05 : Drop the ordering table

DROP TABLE lp_picture_ordering;


CAVEATS

CAVEAT #1

You needed to add the date_submitted index so the retrieval ordered by date would be fast

CAVEAT #2

You will only need to do STEP 01 once

CAVEAT #3

STEP 02 creates the increment values for every plate_uid. This technique is only possible with the MyISAM Storage Engine. In my earlier posts, I have suggested using an auto_increment column in a multiple column PRIMARY KEY:


Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Jun 10, 2012 : MySQL get next unique value without auto increment
Feb 26, 2013 : How to use 2 auto increment columns in MySQL phpmyadmin
Aug 28, 2013 : How to go about modeling the main object of relationship?


CAVEAT #4

In STEP 03, the id values are generated by the Storage Engine.

CAVEAT #5

The ordering table includes uid with a UNIQUE KEY so that STEP 04 can do an INNER JOIN

CAVEAT #6

When your done, no need to keep the ordering table. That's why I drop it.

CAVEAT #7

Should you ever need to do this again, just do Steps 2-5

Give it a Try !!!