Eu criei um novo conjunto de dados que possui mais de 11.000.000 de linhas com 4 tabelas dinâmicas no MySQL. As tabelas não são tão profundas, apenas 6 a 12 colunas por.
Minha pergunta é que configurei o Lucene Apache SOLR para indexar os dados e funciona muito bem para pesquisar.
Não notei um tempo de carregamento dramático nas pesquisas. Se eu executar um comando SQL bruto, a diferença entre os dois será insignificante. Em que ponto é mais benéfico usar um mecanismo de indexação do que criar scripts de uma consulta bruta?
Now granted I have the ability to set "weight" and all that via SOLR .. But my impression was that I would have dramatically reduced overhead on the server ... Is it just that my dataset isn't large enough / complicated enough to illicit these finding? Other than the obvious advantages being weight, rank and sort/filter -- What is the overhead advantage of using a indexing system such as SOLR?
An abstracted layer for indexing is never going to be better or more performant than a correctly architected database structure. The purpose of it is instead to be a tool to make it easier for someone who is inexperienced with implementing a correctly architected structure.
We'd have to see the table structure, the index definitions, the query you ran, and ideally it's query plan (
EXPLAIN ANALYZE) to be able to specifically comment on how efficiently it's operating. But generally if you're just doing straightSELECTs against a table, they'll run relatively well, and when indexed properly should run efficiently.