SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    There is no general chat z0s0's Avatar
    Join Date
    Aug 1998
    Location
    Melbourne
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ENUMs, indexes = slow queries? (mysql)

    I'm facing some very confusing results here, so I'll try to articulate them as well as I can:

    I have a table with 30,000 rows, each timestamped, and categorised into 1 of 2 possible categories.

    The timestamp column (which is actually an integer - it's unixtime) has an index on it.

    And the category column is of type ENUM('A','B'), and also has an index on it.

    I want to retrieve the most recent 50 rows, of a specific category (either 'A' or 'B').

    So the query options are:

    Code:
    SELECT * FROM mytable WHERE category & 1 ORDER BY stamp DESC LIMIT 60
    (this takes 0.01s to complete and is the best bet)

    Code:
    SELECT * FROM mytable WHERE category = 1 ORDER BY stamp DESC LIMIT 60 
    
    OR 
    
    SELECT * FROM mytable WHERE category = 'A' ORDER BY stamp DESC LIMIT 60
    (both these options take 0.8s because for some reason they do not use the index and instead rely on filesort)

    First of all, I can't understand why the latter 2 fail to use the index and hence perform very poorly.

    But where it gets even more confusing is when I try to query the 50 most recent records from category 'B'. There are 0 records currently in category 'B', nonetheless the result now becomes:

    Code:
    SELECT * FROM mytable WHERE category = 'B' ORDER BY stamp DESC LIMIT 60 
    
    OR
    
    SELECT * FROM mytable WHERE category = 2 ORDER BY stamp DESC LIMIT 60
    (these become the best option, and take 0.0s to complete)

    While:

    Code:
    SELECT * FROM mytable WHERE category & 2 ORDER BY stamp DESC LIMIT 60
    becomes the bad option, taking 1.74s because it now fails to use the index.

    So to summarise: Using the & comparison operator uses the index correctly when querying records which do exist, while the = operator uses the index correctly when searching for records which do not exist.

    I'm really stuck here. Anyone have any ideas?
    Wormly Server Performance Monitoring
    Don't wait for an SMS at 4am. Find out what's really
    going on and fix the problem. www.wormly.com/website-monitoring

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm, yeah, that is very odd. i wonder if MySQL handles indexed ENUMs differently (buggy?).

    are you only going to have A and B type values or not? if so, maybe you could use a TINYINT (or bigger, if needed) and store 0 and 1 instead?

    MattR, DaveMaxwell, etc., any ideas?


    BTW, if you're not searching on the stamp column, you might as well lose it as a seperate index and make a multi-column index on category and stamp together. maybe you already know that, though. then, i think, MySQL could use the index for the `ORDER BY stamp', too.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    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)
    & is not a comparison operator. & performs bitwise arithmetic on the column..

    When you perform column AND 1 it just results in the column, so it is fast (doesn't have to do any math). column AND 2 requires work.

    But remember that indexes on columns which are highly repetitive (e.g. index on a 'sex' column of M/F, your enum col) are not very useful in query performance (what is called 'not very selective'). MySQL will escalate to a table-scan any way after 30% row scans (I think) since it is far cheaper than trying to look at the index.

    MySQL is limited to using a single index on a table per query, so having two will not help.

    This might, provided it can sort on the inner cols of an index:
    CREATE INDEX whatever_idx ON yourtable( category, stamp desc )
    Last edited by MattR; Jun 4, 2002 at 11:59.

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    & is not a comparison operator. & performs bitwise arithmetic on the column..

    When you perform column AND 1 it just results in the column, so it is fast (doesn't have to do any math). column AND 2 requires work.
    since ENUMs can be compared with their string values or numerically, i thought & was acting as a comparison operator. if you had an ENUM('foo', 'bar', 'apple') i thought `category & 1' would return all rows that have odd number ENUM values ('foo' or 'apple' in this case). but then, i don't really see how it could use the category index for a bitwise AND.

    z0s0, you didn't say, which index is being used in each query (that would be the `key' column from EXPLAIN)?

  5. #5
    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)
    It sort of will be comparison, in that it will return either true or false for rows, e.g.:

    WHERE category & 1
    turns into:
    WHERE (0 or positive int)

    So it will do weird things.


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
  •