Indexing large DB's with Lucene/PHP

Afternoon chaps,

Trying to index a 1.7million row table with t开发者_JAVA百科he Zend port of Lucene. On small tests of a few thousand rows its worked perfectly, but as soon as I try and up the rows to a few tens of thousands, it times out. Obviously, I could increase the time php allows the script to run, but seeing as 360 seconds gets me ~10,000 rows, I'd hate to think how many seconds it'd take to do 1.7million.

I've also tried making the script run a few thousand, refresh, and then run the next few thousand, but doing this clears the index each time.

Any ideas guys?

Thanks :)

I'm sorry to say it, because the developer of Zend_Search_Lucene is a friend and he has worked really hard it, but unfortunately it's not suitable to create indexes on data sets of any nontrivial size.

Use Apache Solr to create indexes. I have tested that Solr runs more than 300x faster than Zend for creating indexes.

You could use Zend_Search_Lucene to issue queries against the index you created with Apache Solr.

Of course you could also use the PHP PECL Solr extension, which I would recommend.

Try speeding it up by selecting only the fields you require from that table.

If this is something to run as a cronjob, or a worker, then it must be running from the CLI and for that I don't see why changing the timeout would be a bad thing. You only have to build the index once. After that new records or updates to them are only small updates to your Lucene database.

Some info for you all - posting as an answer so I can use the code styles.

$sql = "SELECT id, company, psearch FROM businesses";
$result = $db->query($sql);     // Run SQL

$feeds = array();

$x = 0;
while ( $record = $result->fetch_assoc() ) {
    $feeds[$x]['id'] = $record['id'];
    $feeds[$x]['company'] = $record['company'];
    $feeds[$x]['psearch'] = $record['psearch'];
    $x++;   
}

//grab each feed

foreach($feeds as $feed) {  
  $doc = new Zend_Search_Lucene_Document();  

    $doc->addField(Zend_Search_Lucene_Field::UnIndexed('id',  
    $feed["id"]));  

  $doc->addField(Zend_Search_Lucene_Field::Text('company',  
    $feed["company"]));  

    $doc->addField(Zend_Search_Lucene_Field::Text('psearch',  
    $feed["psearch"]));  

    $doc->addField(Zend_Search_Lucene_Field::UnIndexed('link',  
    'http://www.google.com'));  


  //echo "Adding: ". $feed["company"] ."-".$feed['pcode']."\n";  

  $index->addDocument($doc);  
}  



$index->commit();

(I've used google.com as a temp link)

The server its running on is a local install of Ubuntu 8.10, 3Gb RAM and a Dual Pentium 3.2GHz chip.