In the last episode (May 16), R. Henry Leukart III said:
> Thanks for the help on this. One interesting thing is that my
> conditions only match 6,369 (about half) of my rows, so I do think
> it's strange that it's not using the index on those grounds.
Actually, indexes are usually only used at the 5-10% mark nowadays; the
time spent seeking the disk head from index to data and back for every
record usually makes the index lookup much slower than a simple full
table scan. If your condition matched 1/2 the records, mysql is going
to have to hit every disk block in the table to retrieve the data
anyhow (another reason for the 5-10% breakpoint; how many records fit
in a disk block? Estimate an 8192-byte block, and 200-byte records).
> But, I'm even more interested in suggestions on how I can speed up
> the query. There MUST be a fast way to get the most recent five
> newspaper articles as of a given date in a large database such as
> this. What do sites like Slashdot and the NY Times know that I
> don't? How could I do this quickly?
The Times probably uses Oracle or one or one of the other major
databases. They all can transparently partition tables into smaller
subtables by an arbitrary condition; it'd make sense to partition by
date here. Create partitions with a break every month or so. You can
do the same manually in MySQL by having "news_new" and "news_old"
tables, migrating month-old news from "new" to "old" every night, and
building your queries to select from "new" first, and if there are less
than 25 (say) results, fill the rest of the resultset by selecting from
"old". Dunno how Slashdot does it.
--
Dan Nelson
dnelson@stripped
