On Dec 4, 2003, at 4:05 PM, Frank Barknecht wrote:
> Frank Barknecht hat gesagt: // Frank Barknecht wrote:
>> Now, how to put this to use?
> And related: This does not count the results from where-claused
> queries like: "SELECT COUNT(*) FROM normal WHERE artist LIKE
> '%Britney%';", or does it (somehow)?
My impression is that this is an internal value kept to do query
optimization (since table size effects that considerably). It may not
be entirely accurate, and doesn't get updated immediately. It can't be
used with any query.
If you want a query to be fast, you may want to use full-text search,
or otherwise partition your table more, e.g., an artist table, and you
do something like "SELECT COUNT(normal.id) FROM normal, artist WHERE
normal.artist_id = artist.id AND (artist.first_name = 'Britney' OR
artist.last_name = 'Britney')". Everything in that query can be
indexed, and so it will be quite fast (and more importantly, scale to
large databases). LIKE doesn't scale well (but full text scales
If you are doing some sort of paging, you might simply want to cache
the results of the count. Also, if you are doing paging with ordering
(which you probably want), then paging and slicing will only save you
from fetching and constructing all the objects, but the database still
has to look at all the rows (because it can't sort them until it does
that). So it might be faster to pull and cache the full results once,
on the assumption that the user will be looking at later results
Hmm... a useful feature would be to implement equality and hashing for
query objects (which are immutable). Then you could construct a
query, and use that as a dictionary/cache key for the select results.
That might be too difficult, though, because AND(a, b) == AND(b, a),
but those logic relationships are hard to find. You can always get the
query's string/SQL representation, and cache based on that.
Ian Bicking | ianb@... | http://blog.ianbicking.org