Хотелось бы услышать мнение тех кто этой функцией пользуется. В каких случаях она идеальна, а в каких всё таки не стоит ей пользоваться?

Дык, это какой-то mysql-ный хак. Он позволяет оставить часть результата серверу mysql и не перекачивать его в область памяти php сразу, как это делает mysql_query.
Из плюсов - результат запроса доступен практически сразу после самого запроса. Из минусов - выполнять другие запросы  к mysql пока не выкачали или не пристрелили результаты этого - нельзя. Нельзя сразу определить количество строк в результате и так далее, смотри в доке. Хак он и есть хак.

Наиболее здравое применение - полное чтение больших таблиц для какой-то странной надобности. Когда таблица реально большая, select * запихает весь результат в память и может настать коллапс памяти значительно раньше, чем программер предполагает. 
Обычно, всякие дамперы именно эту функцию и используют. Отсутствие такого хака для других драйверов баз данных, сильно мешает созданию универсальных дамперов-восстановителей для баз данных. По этому, существующие php-дамперы работают преимущественно с mysql.

т.е. в большенстве случаев и в "обычной жизни" эта функция бесполезна?

Например индексация в Sphinx через xmlpipe

Можно конечно усложнить код и гонять запросы с BETWEEN, а можно использовать этот режим.