SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    WHERE field = XXX vs WHERE field <> XXX : Performance the same?

    Hi everyone, is there any performance difference btw the following WHERE criteria.

    WHERE abcfield = 'XXXX'

    vs

    WHERE abcfield <> 'XXXX'

    A)There are same number of records that for each of the above criteria
    B) abcfield is indexed.

    This has been puzzling me. At first i thought that WHERE abcfield <> 'XXXX' would be slower, but thinking abt how mysql uses indexes, it might not necessarily be the case.

    Thanks!!!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i've never actually tested this, but rudy has hinted that <> and != are slower than =.

    do you have a dataset you can test against?

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    mysql> create table foo (foo tinyint not null);
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> insert into foo values (1), (1), (1), (2), (3), (4);
    Query OK, 6 rows affected (0.00 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    
    mysql> insert into foo select * from foo;
    Query OK, 6 rows affected (0.00 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    
    <snip>
    
    mysql> insert into foo select * from foo;
    Query OK, 6291456 rows affected (17.53 sec)
    Records: 6291456  Duplicates: 0  Warnings: 0
    
    mysql> alter table foo add index (foo);
    Query OK, 12582912 rows affected (2 min 7.64 sec)
    Records: 12582912  Duplicates: 0  Warnings: 0
    
    mysql> select count(*) from foo where foo = 1;
    +----------+
    | count(*) |
    +----------+
    |  6291456 |
    +----------+
    1 row in set (7.45 sec)
    
    mysql> select count(*) from foo where foo = 1;
    +----------+
    | count(*) |
    +----------+
    |  6291456 |
    +----------+
    1 row in set (7.39 sec)
    
    mysql> select count(*) from foo where foo != 1;
    +----------+
    | count(*) |
    +----------+
    |  6291456 |
    +----------+
    1 row in set (10.72 sec)
    
    mysql> select count(*) from foo where foo != 1;
    +----------+
    | count(*) |
    +----------+
    |  6291456 |
    +----------+
    1 row in set (10.73 sec)

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    woh! thanks for the real world test!

    i guess 50% slower isnt THAT bad...

    I doubt i'll ever be doing counts on index in the 6million range though...

    Thanks for the great test longneck! For some reason i never thought of actually trying it out...


    I did some times on my own data set, and for some reason query times were exactly the same! ~ 0.1secs...

  5. #5
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Read somewhere (probably SQL for Smarties) about _possible_ optimization for <>

    SELECT * WHERE foo < 1 UNION SELECT * WHERE foo > 1

    Which is more likely to use an index on some databases, obviously need to test wether MySQL is one of them.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the explain in my queries above does use the index.


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
  •