List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 17 2000 4:29am
Subject:Re: Slow Query Using <=
View as plain text  
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
Thread
Slow Query Using <=R. Henry Leukart III16 May
  • Re: Slow Query Using <=sasha16 May
  • Re: Slow Query Using <=sinisa16 May
    • RE: Slow Query Using <=R. Henry Leukart III17 May
      • RE: Slow Query Using <=Daniel Bohling17 May
      • Re: Slow Query Using <=Dan Nelson17 May
      • RE: Slow Query Using <=sinisa17 May
  • Re: Slow Query Using <=Jan Dvorak17 May
    • RE: Slow Query Using <=R. Henry Leukart III18 May
      • Re: Slow Query Using <=Jan Dvorak18 May
        • RE: Slow Query Using <=R. Henry Leukart III19 May
          • Re: Slow Query Using <=Jan Dvorak19 May
            • RE: Slow Query Using <=R. Henry Leukart III20 May
              • Re: Slow Query Using <=Benjamin Pflugmann20 May
                • RE: Slow Query Using <=R. Henry Leukart III20 May
                  • Re: Slow Query Using <=sasha20 May
                    • RE: Slow Query Using <=R. Henry Leukart III20 May
                      • Re: Slow Query Using <=sasha20 May
              • Re: Slow Query Using <=Jan Dvorak22 May
      • Re: Slow Query Using <=sasha18 May
      • Re: Slow Query Using <=sasha20 May