mySQL query to update just 150 most recent entries ? Help...

[SlothVader]

I have a constantly growing database.  With each automated addition to the database a lot of junk characters get picked up.  So I run a little script that uses the mysql_query to update and replace the characters from the field that I don't want.

 

For example... in the following example, I am telling the mysql to go into the field called 'Title' and every time it finds the '#' character, to simply replace it with nothing.

 

$fixshitupy =  mysql_query ("UPDATE content SET Title=replace(Title,'#','')") or DIE("no query");
mysql_query($fixshitupy);

 

And the above code works like a charm.  It rips through the entire database, searches every entry in 'Title' and replaces the character I am seeking to replace. 

 

BUT... the problem is that it is having to search the entire database when it carries out this query, when really it would be effective to only carry out this command against the last 150 entries.    With 900,000 entries in the database it is kind of wasteful running the same query over 'Titles' that have been previously done.

 

So for purposes of speeding up the execution of this query and not having it waste a moment going over the previously 'fixed' Titles, I was wondering if there was a way to modify my above query to only perform its action against ONLY the last 150 entries in the database.   

 

I am a complete newb when it comes to php or mysql - and the mere fact that my mysql query works is simply a testament to some luck rather then knowledge.  So hopefully someone could enlighten me to how I can get the query to only be run against the last 150 entries.

 

Thanks in advance..

 

S

 

 

 

[revraz]

Does each row have a unique ID or entry date?

[fenway]

Provided you can use an order by clause, you can LIMIT 150.

[SlothVader]

Yes... each entry has two unique identifiers...

 

The first is : ID  which is automatically assigned and incremented as each new entry is added to the database.

 

The second is : timestamp  which is automaticallly added.  BUT I am not sure where the timestamp originates from... as to whether it somehow reflects the originating data of the content that is being added or the date the actual data was included into the database.  Here for example are the most recent timestamps, with last entry being the last entry in the database

 

1198515943

1198515949

1198516068

1198516079

1198530312

1198530357

1198530488

1198544783

1198544878

1198573568

1198573568

1198587910

1198602456

 

Since I am a complete newb, even when it comes to timestamps, I really don't know if those times of the entries are only seconds apart or something else.  They do appear to be ascending as each new entry was added.  So possibly they do reflect the time of the entry's addition to the database.

 

So that presents two possible scenarios... where the mysql operation could be confined to just being run against the last 150 ID or within the last 12 hours of the most recent timestamp ?

 

.

 

 

[SlothVader]

Provided you can use an order by clause, you can LIMIT 150.

 

Does that automatically know that it should apply to only the last 150... or would it only limit it to the first 150 instances ?

 

And if it does automagically apply against the last 150 entries, would you insert it into the code like this :

 

$fixshitupy =  mysql_query ("UPDATE content SET Title=replace(Title,'#','') LIMIT 150") or DIE("no query");
mysql_query($fixshitupy);

 

or have I inserted it incorrectly for positioning within the quotes or bracket ?

 

or to apply the the limit to a clause like WHERE ID LIMIT 150 has to be used ?

 

$fixshitupy =  mysql_query ("UPDATE content SET Title=replace(Title,'#','') WHERE ID LIMIT 150") or DIE("no query");
mysql_query($fixshitupy);

 

 

.

[SlothVader]

 

Or with that LIMIT 150 do I have to do an ORDER BY ID

 

$fixshitupy =  mysql_query ("UPDATE content SET Title=replace(Title,'#','') ORDER BY ID DESC LIMIT 150") or DIE("no query");
mysql_query($fixshitupy);

 

if that is approximately correct... would the same idea work with the timestamp ?

 

$fixshitupy =  mysql_query ("UPDATE content SET Title=replace(Title,'#','') ORDER BY timestamp DESC LIMIT 150") or DIE("no query");
mysql_query($fixshitupy);

 

.

[fenway]

Using anything other than the ID for sorting records is preferred... using LIMIT without an ORDER BY clause can produce unexpected results.

[SlothVader]

 

I tried using the following, but it did not seem to do anything.... and returned a  " No Query " response

 

$fixshitupy =  mysql_query ("UPDATE content SET Title=replace(Title,'#','') ORDER BY ID DESC LIMIT 150") or DIE("no query");
mysql_query($fixshitupy);

 

So I an suspecting that I don't have the sequence of commands in the correct order.. ( ain't I a total newb...

 

UPDATE content SET Title=replace(Title,'#','') ORDER BY ID DESC LIMIT 150

 

Is it a problem with where I put the 'ORDER BY ID DESC LIMIT 150 in the command that is in the wrong spot... or am I missing some brackets...

 

.

 

[fenway]

Well, issue the equivalent select and make sure everything's as expected.

[SlothVader]

Uh... you have to remember that I am a total newb here and so I really don't understand what you mean...

 

Possibly if you could put what you are suggesting into an example I can figure it out...

 

Sorry...

 

( It all reminds me of an episode on the Simpsons.... where Bart is talking to his dog.  But all the dog hears is " whah whah whhaaah whah whahhhhhhhhh ".  And Bart wonders why the dog won't lay down or shake a paw like he is asking the dog to do....    The dog just stands there with a puzzled look on its face

[fenway]

Try "SELECT title, replace(Title,'#','') as NewTitle FROM content ORDER BY ID DESC LIMIT 150" and see what happens.