SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    When is the index used?

    Hi,

    Let us assume that I create the following index on one of my tables.

    Code:
    CREATE INDEX added ON table ( date_field )
    I know (at least I think I do ) that the index will be used when I execute the following query.

    Code:
    DELETE FROM table WHERE date_field = CURDATE()
    My question is this. Will the index be used when I execute the following query?

    Code:
    DELETE FROM table WHERE date_field < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
    Yours, Erik.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i think yes

    because the expression on the right side of the operator will resolve to a specific value that is the same datatype as the column on the left side
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Alright. Thanks for the explanation!

    Yours, Erik.

  4. #4
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,207
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    should foreign key be indexed?

    table1

    (table1_ID) table1_say
    (1) France
    (2) America
    (3) Japan

    table2

    (table2_ID) table2_say
    (2) New Yok
    (1) Paris
    (2) Chicago
    (3) Tokyo
    I have the data above in myTables.

    Table1_ID is primary.
    Table2_ID is a foreign key connected to table1_ID


    Should table2_ID be indexed?

    Thanks in Advance
    Last edited by dotJoon; Nov 18, 2004 at 03:19.


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
  •