Take the 2-minute tour ×
Webmasters Stack Exchange is a question and answer site for pro webmasters. It's 100% free, no registration required.

Where can i find really large databases .sql files with 100's MB or a few GB text, 10.000.000 plus rows of text so I can test my sphinx search engine indexing

share|improve this question
add comment

3 Answers 3

I often use GenerateData.com, which has lots of data types you can use and can generate SQL code as well as CSV etc. Don't know how well (or if) it would handle millions of rows, though.

share|improve this answer
add comment

In addition to danlefree's excellent answer, you can find all text on the Stack Overflow network here. If you are using an MSSQL server this dataset might be easier to import (since Stack Overflow uses MSSQL).

share|improve this answer
    
Where can one learn more about SE implementation? Is it open source? –  Chris Sep 29 '10 at 19:22
1  
@Chris: No Stack Overflow / Stack Exchange is not open source, only the user-generated content is Creative Commons Wiki licensed. Try blog.stackexchange.com –  Jesper Mortensen Sep 29 '10 at 19:42
    
thanks. –  Chris Sep 29 '10 at 22:56
add comment

If you really want to spend that kind of time downloading, here's a link to the Wikipedia dumps - "WARNING: 6.07 GB compressed, approximately 27 GB uncompressed as of 2010-09-20" (and you'll still have only 3,428,557 articles)

Here's a quick PHP script that'll make one for you (just create an empty DB, temporarily grant the $db_user CREATE + INSERT privileges, and populate the configuration variables in the script):

<?php

$db_host = '127.0.0.1';
$db_name = 'testing';

$db_user = 'root';
$db_pass = '';

$entries = 10000000;
$entry_words_min = 250;
$entry_words_max = 1000;

/*
  End configuration
*/

function get_rand_word( $len_min, $len_max ) {
    for ( $i = 0; $i < ( rand( 0, $len_max - $len_min ) + $len_min ); $i++ ) {
        $word .= chr(rand(65, 90));
    }
    return $word;
}
function get_title() {
    for ( $i = 0; $i < ( rand( 4, 10 ) ); $i++ ) {
        $title .= get_rand_word( 2, 9 ) . ' ';
    }
    return $title;
}
function get_fulltext() {
    for ( $i = 0; $i < ( rand( 250, 500 ) ); $i++ ) {
        $fulltext .= get_rand_word( 2, 9 ) . ' ';
    }
    return $fulltext;
}

$dsn = 'mysql:dbname=' . $db_name . ';host=' . $db_host;

try {
    $dbh = new PDO($dsn, $db_user, $db_password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    die();
}

$dbh->query('CREATE TABLE IF NOT EXISTS `sphinx` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(150) collate utf8_bin NOT NULL,
  `fulltext` text collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin');

$sth = $dbh->prepare('INSERT INTO `sphinx` (`title`,`fulltext`) VALUES (:title, :fulltext)');

$counter = 0;
for ( $i = 0; $i < $entries; $i++ ) {
    $sth->execute(array(
        ':title' => get_title(),
        ':fulltext' => get_fulltext()
    ));
    $counter++;
}

echo $counter . ' rows inserted';

?>

Note: You'll probably need to change your PHP environment configuration or tweak the number of entries to run this script...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.