SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow SELECT, too many rows examined.

    # Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 32023
    SELECT id FROM entries WHERE active='1' AND preview='0' ORDER BY posted DESC LIMIT 10,1;

    If the query is limited to 10 results, why does it examine all rows? Is there any way to make the query stop once it's got enough rows? (This is literally killing my website.)

    posted is a date field, every field that's used with = is indexed

    What can I do?

  2. #2
    Non-Member
    Join Date
    Jan 2004
    Location
    Los Angeles
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    think about what your query is doing...
    it has to go through your entire table finding where active = 1, then even if it hits 10 it has to process the rest of the table because now you want to ORDER BY a field. So that means it has to gather up ALL the data in the table, then take the first 10 records in descending order.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I understand. But how on Earth do I get number of the newest rows without processing the whole table? There's got to be some way!

  4. #4
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $count=1
    while($row=mysql_fetch_assoc($result)) {
    $count++
    if(
    $count == 10) {
    // stop it 

    If knowledge is power - Why isn't our army librarians?!
    Statistics show that 63% of all statistics are fake.
    When i was little i broke my neck, and i havent looked back since .
    I completed the internet in 1 week. The end boss was pretty easy though .

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Los Angeles
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually 1337 that would be a really inefficient way of doing it. What you're doing is getting ALL the matching database rows then in php just displaying the first 10.

    If there were a million rows in the DB you'd be wasting a ton of bandwidth. The correct way would be to do an order by on a date field or some field you want to limit by, then do either a TOP(sql server) for a LIMIT(mysql).

    This way the database isn't returning wasted rows.

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Add an index on Posted, should speed up rowscan.

    WWB

  7. #7
    SitePoint Member cumquott's Avatar
    Join Date
    Aug 2004
    Location
    Pennsylvania
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, it seems that even if the right indexen are used, MySQL (that's what I'm using) still won't use the correct indexes. For example, these two queries give the same result (both fields are indexed and I ran OPTIMIZE TABLE):

    select max(MI01_PART_NO) from PRTMST
    where MI01_WHSE = 1 and MI01_PART_NO < 'COM-056'

    -or-

    select MI01_PART_NO from PRTMST
    where MI01_WHSE = 1 and MI01_PART_NO < 'COM-056'
    order by MI01_PART_NO desc limit 1

    The second one makes the server HDD grind pretty good. EXPLAINs on each of these give, respectively:

    Row Field Value
    1 table PRTMST
    1 type ALL
    1 possible_keys MI01_PART_NO,MI01_WHSE
    1 key
    1 key_len
    1 ref
    1 rows 50763
    1 Extra where used

    Row Field Value
    1 table PRTMST
    1 type range
    1 possible_keys MI01_PART_NO,MI01_WHSE
    1 key MI01_PART_NO
    1 key_len 27
    1 ref
    1 rows 50763
    1 Extra where used; Using filesort

    I want a not too dissimilar result: quickly get the previous record. Using the same technique (limit 1) to get the next record is quite fast. This is "moon surface erosion slow".

    Ideas?

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    recchi_8: Bear with me, but "posted" is a date field, so isn't my query already doing what you say to do? But it scans the entire table...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •