I have a lot of entries in a database table, and I want to maximize the
performance of an SQL search that uses “like” Is it true that for
queries involving multiple colums, searches faster when you create a
view from the database using, say, one criteria, and then running the
search on the subset of your table that resides in the view?
Thanks!
On 05/02/2008, Cheri R. [email protected] wrote:
I have a lot of entries in a database table, and I want to maximize the
performance of an SQL search that uses “like” Is it true that for
queries involving multiple colums, searches faster when you create a
view from the database using, say, one criteria, and then running the
search on the subset of your table that resides in the view?
Thanks!
No.
–
Thomas P.
[email protected]
[email protected]
Büro: 030 - 830 353 88
mobil: 0176 - 75 03 03 04
Privat: 030 - 49 78 37 06
acts_as_ferret
acts_as_sphinx
acts_as_solr
Let me clarify. I create a view based on an indexed column and then
perform the “LIKE” search on the view.
Thomas P. wrote:
On 05/02/2008, Cheri R. [email protected] wrote:
I have a lot of entries in a database table, and I want to maximize the
performance of an SQL search that uses “like” Is it true that for
queries involving multiple colums, searches faster when you create a
view from the database using, say, one criteria, and then running the
search on the subset of your table that resides in the view?
Thanks!No.
–
Thomas P.
[email protected]
[email protected]
Büro: 030 - 830 353 88
mobil: 0176 - 75 03 03 04
Privat: 030 - 49 78 37 06
http://thopre.wordpress.com/
http://www.thopre.com/
On 05 Feb 2008, at 22:09, s.ross wrote:
acts_as_ferret
acts_as_sphinx
acts_as_solr
Other sphinx rails plugins:
http://blog.evanweaver.com/files/doc/fauna/ultrasphinx/files/README.html
Sphinx is really great, it’s best used for mostly static sites (or
you’ll have to use thinkingsphinx’ delta field to manage a delta index).
I just recently had horrid experiences with ferret (yes, even with
the drb server running production) on several production machines,
but other people use it with success.
I know someone who’s using acts_as_solr with great success, although
you do have to live with a fairly big Java footprint and you might
need to play with the horrendous xml configs a bit depending on your
goals.
And let’s not forget acts_as_searchable, which uses hyper estraier.
The plugin is a bit outdated, but still works very well for me. Hyper
estraier uses very little memory and is blazingly fast in the app I
use it in.
If I had to choose, I’d go for sphinx (and a lot of rails developers
seem to agree with me) for mainly static data apps or hyper estraier
for data intensive apps (people might disagree here, but it has been
great for me).
Best regards
Peter De Berdt
On 05 Feb 2008, at 23:38, Michael Whittaker wrote:
I have a lot of entries in a database table, and I want to
maximize the
performance of an SQL search that uses “like” Is it true that for
queries involving multiple colums, searches faster when you
create a
view from the database using, say, one criteria, and then
running the
search on the subset of your table that resides in the view?
Unindexed searches like the MySQL LIKE searches are very resource
intensive and slow on lots of data. Views won’t help you here. Use a
real fulltext indexer instead, that’s what they exist for.
Best regards
Peter De Berdt
I don’t think creating a view will be faster…
Try this if applicable, basic tehcnique at the bottom with < and >
works as well with MySQL.
http://thoughts.n79.org/2006/03/07/optimizing-an-sql-like-query/
On 5 Feb., 22:43, Cheri R. [email protected]
That’s true, we gained like 3000% performance after indexing a 4
GiByte-log-file-table-column (< 1sec after in comparison to 20-30
seconds of query time).
Although it can take a long time to index, MySQL does a pretty good
job in searching in the index afterwarts.
On 05/02/2008, Cheri R. [email protected] wrote:
Let me clarify. I create a view based on an indexed column and then
perform the “LIKE” search on the view.
A view is only a “view” to a real database-table. For example you can
allow
specific users of your database not to be able to see all the columns of
database-table - for this purpose you would create a view.
If you do a search like “LIKE ‘…%abc%’…” on a table, the complete
table
has to be scanned. There is no difference if it is a view - the
complete
table has to be scanned for the value.
You can increase the speed if you are doing a “…LIKE ‘abc%’…” on an
indexed column.
-Thomas
–
Thomas P.
[email protected]
[email protected]
Büro: 030 - 830 353 88
mobil: 0176 - 75 03 03 04
Privat: 030 - 49 78 37 06