CODEWITHSUNDEEP
mysqlperformancesphinx
artyomboyko
artyomboyko

Reputation: 2861

Sphinx get millions of results

I use MySQL + Sphinx to store many millions rows of data. We have website to view all information from our database.

For example, movie titles (100,000,000 rows). I need to view ALL of them on our website, 100 titles per page. Also, i need view them sorted by actors popularity.

For the first 10 pages all works great. But after that i reached max_matches limit. Increasing this limit will force sphinx to use more CPU/RAM.

Also, i can't even set max_matches to 20,000,000.

WARNING: max_matches=20000000 out of bounds; using default 1000

I can use MySQL to perform queries like this:

SELECT * FROM titles WHERE tid >= $start AND tid <= $end

to use tid index. But i can't sort it by tid. I need to sort my titles by info from other tables.

What is the best way to get access to many millions of rows, sorted and do it quick. Please help.

UPDATE: from sphinx source: /src/searchd.cpp

if ( iMax<0 || iMax>10000000 )
{
    sphWarning ( "max_matches=%d out of bounds; using default 1000", iMax );
} else
{
    g_iMaxMatches = iMax;
}

Is 10000000 is a limit ? How can i get offset more than that ?

Upvotes: 0

Views: 2848

Answers (2)

barryhunter
barryhunter

Reputation: 21091

If your sort order is based on an attribute (actors popularity) - rather than sphinxes computed weight - then can just use cursors instead.

Because can filter by this attribute.

http://sphinxsearch.com/forum/search.html?q=cursors&f=1

I do have to wonder tho, if you REALLY need it. At 100 items per page, a max_matches of 1M, means 10,000 pages of results. Do your visitors really go though reading 10,000 pages of results?

Upvotes: 3

Dmitri
Dmitri

Reputation: 9157

Use LIMIT (select api) to retrieve a specific page.

Anything you want to order by needs to be included in the index, you cannot use joins here.

Upvotes: 1

Related Questions