How to reduce mysql CPU usage

[smarty_pockets]

I have a PHP script that reads about 100,000 records from a mysql database, does some processing, and then inserts them into a new table. I want this to run every week or so via a cron job. The problem is, when this script runs, mysql takes 100% of my CPU. Any ideas on how I could solve this? Since this is a backup cron job, it doesnt have to be super fast or efficient. It just shouldn't affect my public website.
Thanks for any help!

[longneck]

you should figure out how to do the processing in mysql instead of PHP. if you have to resort to temp tables, that will probably be better.

as an absolute last resort, you can process the changes in small batches instead.

can you explain what processing you're doing? or post your code?

[smarty_pockets]

I dont think I can do the processing in mysql in my case. Also, temp tables wont work either. Processing the changes in smaller chunks might be possible, though will require more work to set up. my code is pasted below. Its basically a search indexer, where each search term is given a particular score depending upon certain criteria. The main problem is that theres a lot of data to process...2 nested loops with 50k x 10k entries.

Thanks for the help!

<?php
include_once("config.php"); //database connection is created here

$sql = "Select keyword from search_keywords"; //this has about 50k rows

$rs = $conn->execute($sql);
while(!$rs->EOF)
{
    $keyword = $rs->fields['keyword'];

    $sql1 = "select ID, title, description from data_entries"; //this has about 10k rows
    $rs2 = $conn->execute($sql1);
    while(!$rs2->EOF)
    {
        $id = $rs2->fields['id'];
        $title = $rs2->fields['title'];
        $description = $rs2->fields['description']; 

        $score = 0;
        if(strpos($title, $keyword))
            $score = $score + 20;
        if(strpos($description, $keyword))
            $score = $score + 10;

        if($score > 0)
        {
            $sql_fin = "insert into search_index entryID={$id}, keyword={$keyword}, score={$score}";
            $conn->execute($sql_fin);
        }
        $rs2->movenext();
    }
    $rs->movenext();
}
mysql_close();
?>

[longneck]

that is completely doable in sql:

insert search_index
     ( entryid
     , keyword
     , score )
select d.id
     , k.keyword
     , sum(case when locate(k.keyword, d.title) > 0  then 20 end) +
       sum(case when locate(k.keyword, d.description) > 0  then 10 end)
  from data_entries d
     , search_keywords k

but i have to ask why you're doing this because you can do exactly this with FULLTEXT indexes.

[smarty_pockets]

Wow...I didnt know sql was that powerful! I am currently doing this with FULLTEXT indexes, but the results I'm getting with that are not very accurate in some cases (i want search to be really accurate on the website). The idea is to include more of these checks over time to make the search better.
By the way, any good sql books on online tutorials you recommend?
Thanks a lot for all the pointers!

[longneck]

I am currently doing this with FULLTEXT indexes, but the results I'm getting with that are not very accurate in some cases (i want search to be really accurate on the website).

then you probably need to switch to BOOLEAN mode.

[longneck]

oops, i forgot the group by clause:

insert search_index
     ( entryid
     , keyword
     , score )
select d.id
     , k.keyword
     , sum(case when locate(k.keyword, d.title) > 0  then 20 end) +
       sum(case when locate(k.keyword, d.description) > 0  then 10 end)
  from data_entries d
     , search_keywords k
group
    by d.id
     , k.keyword

[smarty_pockets]

The problem I was facing was that boolean mode worked well with some queries, and not at all with others. Using a regular full text search, the more common search queries (queries that appear more frequently in the fields being searched) worked fine, but i didnt get a lot of results for infrequent search queries. When I changed to boolean mode, the infrequent query results got better, but the regular query results lost their revelance. I guess there is no way to fine tune FULLTEXT searches.

[longneck]

what do you mean by "infrequent search queries"?

[smarty_pockets]

Sorry...I meant queries for uncommon words, that do not occur a lot in the test.