Professional Web Applications Themes

Diffrence in 'add index' question - MySQL

Suppose table: CREATE TABLE `testtable` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `fieldA` INT NOT NULL , `fieldB` INT NOT NULL , `fieldC` INT NOT NULL , `fieldD` INT NOT NULL ) ENGINE = MYISAM ; What's the diffrence between: ALTER TABLE `testtable` ADD INDEX ( `fieldA` ) ALTER TABLE `testtable` ADD INDEX ( `fieldB` ) ALTER TABLE `testtable` ADD INDEX ( `fieldC` ) ....And: ALTER TABLE `testtable` ADD INDEX ( `fieldA` , `fieldB` , `fieldC` ) ; Take care, Quarco...

  1. #1

    Default Diffrence in 'add index' question

    Suppose table:


    CREATE TABLE `testtable` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `fieldA` INT NOT NULL ,
    `fieldB` INT NOT NULL ,
    `fieldC` INT NOT NULL ,
    `fieldD` INT NOT NULL
    ) ENGINE = MYISAM ;

    What's the diffrence between:
    ALTER TABLE `testtable` ADD INDEX ( `fieldA` )
    ALTER TABLE `testtable` ADD INDEX ( `fieldB` )
    ALTER TABLE `testtable` ADD INDEX ( `fieldC` )

    ....And:
    ALTER TABLE `testtable` ADD INDEX ( `fieldA` , `fieldB` , `fieldC` ) ;


    Take care,
    Quarco


    Q Guest

  2. #2

    Default Re: Diffrence in 'add index' question

    Q wrote: 

    The former will create 3 indexes, allowing you fast access (and or sorting)
    to data based on any of the 3 fields.
    The latter will only build one index and the speed improvement will
    (broadly) be limited to queries based on fieldA.


    Paul Guest

  3. #3

    Default Re: Diffrence in 'add index' question

    Q wrote:
     


    The first will create 3 separate indexes, one for each of fieldA, B and C.
    This will vastly speed up queries like SELECT * FROM testtable WHERE fieldB
    = 1;

    The second will create one index on all three fields. The will vastly speed
    up queries that query fieldA, B and C at the same time.

    For example:

    SELECT * FROM testtable WHERE fieldA = 1 AND fieldB = 9 AND fieldC
    = 'Hello';

    Mysql will only use 1 index per table per query so in the first case it
    would use which ever index has the most unique records. Lets assume fieldA
    does.

    It would use the fieldA index to find just the rows where fieldA = 1. But
    after that it must compare all of those rows against fieldB = 9 AND fieldC
    = 'Hello' to get the result. Consider a table with millions of rows, this
    would not be fast.

    However, if you have all three fields indexed together, it will be able to
    find the matching rows from the index alone - much faster.


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

Similar Threads

  1. Index question...
    By howachen@gmail.com in forum MySQL
    Replies: 16
    Last Post: July 20th, 07:09 AM
  2. flash forms/cfgrid db query timezone diffrence
    By none9898 in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 7th, 05:26 PM
  3. Diffrence between WebServices and Remoting
    By Srikanth in forum ASP.NET Web Services
    Replies: 1
    Last Post: April 19th, 02:49 PM
  4. Index question
    By Harry in forum Oracle Server
    Replies: 2
    Last Post: July 2nd, 09:42 AM
  5. Another index question
    By dsb in forum Oracle Server
    Replies: 1
    Last Post: July 1st, 08:46 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139