SitePoint Sponsor

User Tag List

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

    simple index question

    hello,
    i have a request looking like :

    SELECT *
    FROM a_table
    WHERE (field_1 < 2 OR field_2 = 2) AND field_3 = 3

    I have tried a bunch of index combinations (in mysql) and still the EXPLAIN tells me it has to scan the whole table and can't use my indexes.

    What indexes should i use ?
    Can an index be used with a '<' in the where ?
    if field_1 may be only 0 or 1, would i be better to use field_1 IN (0,1) rather than what i am right now ?

    Thanks for any help.

    Quentin
    The largest message boards on the web !
    unblog.fr, hosting 700000 french blogs

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i cannot really answer the mysql index question, since so much depends on the actual contents -- as in all competent database systems, there comes a point where the index will be ignored if it isn't selective enough

    for example, suppose you have a table with many rows, and an index, but the index is on a column (e.g. gender) which has only two values

    many databases will ignore this index, because to find all the rows with gender='F' requires a read of the index file and then a read of the table, so what the heck, that's two reads for half the table, mought as well ignore the index, table scans are faster anyway...
    if field_1 may be only 0 or 1, would i be better to use field_1 IN (0,1) rather than what i am right now?
    well, all i can say about this is that if field_1 can only contain 0 or 1, then you can drop where field_1 in (0,1) altogether, because that'll be true for every row

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru quenting's Avatar
    Join Date
    Dec 2002
    Location
    Switzerland
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well this table would contain may rows, and the 3 fields could have spread values, say, from 0 to 100.

    well, all i can say about this is that if field_1 can only contain 0 or 1, then you can drop where field_1 in (0,1) altogether, because that'll be true for every row
    what i meant was that the only values possible below 2 were 0 and 1, but the field (as the two others) is an unsigned smallint, so there may be many values above 2. The question was really about knowing if it's better for an unsigned int to check if it's < n or IN (1,...,n-1). My guess would be that it's the same, but i'm a beginner .

    Quentin
    The largest message boards on the web !
    unblog.fr, hosting 700000 french blogs

  4. #4
    SitePoint Guru quenting's Avatar
    Join Date
    Dec 2002
    Location
    Switzerland
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was re-reading your answer about the index and thinking about it, do you think that if i'm testing the query on a test table with only a few rows, and that query returns many of these rows, then the indexes could be not selective enough for mysql to show them up in "explain", while when the hundreds of records would be there it would show them ? In that case i should get data comparable to what there will be in production and test the query then ?

    thanks for helping me out in my first steps writing my actual own queries.

    Quentin
    The largest message boards on the web !
    unblog.fr, hosting 700000 french blogs

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, your conclusions are all good

    if there are many values, then IN (0,1) and < 2 should be equivalent

    and yes, you would want the EXPLAIN to be done against a production-size table

    good luck, you're doing fine so far...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •