Sphinx: building 1M docs index having no one real doc

Hi guys

Just want to share an interesting trick on how to easily index something with Sphinx without need of populating database with a lot of data or doing smth like that. The below is a full Sphinx config which lets you build a 1M docs index consisting of random 3-char words and one numeric attribute with a random value. All you need is just any connection to any db (in this case 'mysql -u root' works).

source min
{
    type = mysql
    sql_host = localhost
    sql_user = root
    sql_pass =
    sql_db = test
    sql_attr_uint = attr
    sql_query_range = select 1, 1000000
    sql_range_step = 1
    sql_query = select $start, mid(md5(rand()), 1, 3) body, floor(rand() * 100000 * $end) attr
}
index idx
{
    path = idx
    source = min
}
searchd
{
    listen = 3306
    log = sphinx.log
    pid_file = sphinx.pid
}

As you can see the tricky part is to utilize Sphinx' directives sql_query_range and sql_range_step to let Sphinx loop until it makes 1M docs collection. The drawback is slower indexing comparing to real fetching the same amount of data from db, but come on, you're not going to use this in production, right?

I hope you'll find it helpful when you decide to play with Sphinx.

---

Need help with search infrastructure? Talk to our engineering team — we've built and maintained search systems for over 15 years.

Frequently Asked Questions

How can I generate test data for Sphinx Search without a real database?

Use Sphinx's sql_query_range and sql_range_step directives with a MySQL connection to generate synthetic documents. Set sql_range_step = 1 with a range of 1 to 1,000,000 and use md5(rand()) in the query to produce random text. This creates 1M documents from a single empty database — useful for benchmarking index performance, testing configurations, and validating search behavior before deploying with real data.

What is sql_query_range in Sphinx and when should I use it?

sql_query_range tells Sphinx to fetch data in batches using $start and $end placeholders in the main sql_query. It's essential for indexing large datasets without exhausting MySQL memory — instead of one giant query returning millions of rows, Sphinx fetches data in configurable steps (sql_range_step).

How long does it take to index 1 million documents in Sphinx?

It depends on your hardware, document size and attribute count. The synthetic method using random generation is slower than fetching the same amount of real data, because each row requires a separate SQL round-trip. For production benchmarking, index actual data or at least data with realistic field lengths: random 3-character words won't stress the tokenizer the way real-world text does.

Related Reading

• Announcing DokuWiki Sphinx Search Plugin
• Correct ordering of search result set using Sphinx
• Email me mysql/sphinx query result