SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru quenting's Avatar
    Join Date
    Dec 2002
    Location
    Switzerland
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    not getting it...

    ok I have a table (mysql) with approx 1.5 million entries, and 5 columns.

    I do a
    select distinct column1 from mytable where column2=xxx

    without an index on column 2, the query takes 4.5 secs. the explain sql tells me it goes through the 1.5 million entries (everything normal so far).
    with an index on column 2, the explain sql tells me it has to go through 37000 records (the number for which column2 equals 'xxx', but, the query takes 9 seconds ?!?
    Any explanation ?
    The largest message boards on the web !
    unblog.fr, hosting 700000 french blogs

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Query time is not a good estimation of query efficiency because there are several things that could affect the query time.

    For a real test restart your PC before running each query.

    e.g. create index on column two
    restart PC
    run query several times, record each time
    drop index
    restart PC
    run query several times, record each time
    etc.


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
  •