Total amount of a search

General Discussions - English
1

Hello,

I asked this question on a previous thread but it got lost among many questions. I think it can be interesting for other users.

How can I have the total amount of a search or the total amount of records of an index. In order to have blazing fast searches I need to set up the limit for example limit=1000 but if I want to know how many total records there is for that search what’s the best and fastest way to do? Especially if you want to display total amount of the records in paginated list.

When I do select count(*) from tbl where … I am getting 1000 which is the limit. How can I go beyond?

Thanks,
Laurent

2

Run SHOW META right after the original query.

The total_found gives you, well, the total number of results, despite the LIMIT.

Except in recent versions of Manticore, it may have an automatic ‘cutoff’ (it stops processing once it reaches certain number of hits, so never computes the complete total)

If get total_relation: gte in SHOW META, then the the total_found is not accurate.

See:
https://manual.manticoresearch.com/Searching/Options#cutoff
You can disable the cutoff, if want to be sure to get an accurate total.

3

Thanks, that’s what I used to do but with new version of MariaDB I am getting this error:

[ERROR in query 2] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘META’ at line 1

4

Umm, that suggests you are connected to MariaDB not searchd.

Then again you talk about SphinxSE in another thread, so perhaps using that?

SHOW META is for SphinxQL - or ManticoreQL - where run search queries directly against searchd. Ie connect to searchd directly rateher than via mysql/mariadb server.

Suspect you might actually be better using ManticoreQL directly. Avoids lots of complications.

If really must use SphinxSE (or using FEDERATED) then proabbly COUNT(*) is the the best, but not sure how effient it would be.

Although SphinxSE does provide some details (quiveent to SHOW META) in SHOW ENGINE STATUS

5

Yes it works. Thank you!

SHOW ENGINE SPHINX STATUS; is the correct way for MariaDB.

I’d love to use the FEDERATED engine as I wouldn’t necessary need to use MariaDB but I was not able to make it work. I made a special post about it.