Results 1 to 7 of 7

Thread: Using Btree

  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Thumbs down Unanswered: Using Btree

    CREATE TABLE temp (
    tempvar1 varchar(12) NOT NULL default ''
    ,
    KEY Firstkey USING BTREE(tempvar1)
    )


    Please tell me what is the use of using the key word 'USING BTREE',
    Will it make any difference in the result

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    By doing "USING BTREE(tempvar1)" you are telling MySQL that the index for that key should be created using the Binary Tree algorithm. If you leave USING BTREE off your statement it will default to use whatever algorithm is set as default on your server (usually BTREE).

    MySQL maintains three algorithm types (as far as I am aware). They are BTREE, HASH, RTREE. Each has their own implementation of how to find/insert/delete values for indexes. Defining which you are using for a key is entirely dependent on what you're expecting to be done to that table that maintains the key.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by aschk
    By doing "USING BTREE(tempvar1)" you are telling MySQL that the index for that key should be created using the Binary Tree algorithm.
    If you're dead set on trying to improving performance by overriding the years of experience of the MySQL development team, you should at least know what the words mean.

    A binary tree is a data structure, not an algorithm. Further, the structure MySQL uses is a variant of the b-tree, not a binary tree.

    It shouldn't make any difference in the result. However, it would be prudent to perform regression testing. That is, set up two identical databases, one with and one without the indexes. See if a set of test queries on test data produces bit for bit identical results.

  4. #4
    Join Date
    Nov 2007
    Posts
    3

    *-Tree

    What if i wanted to test another tree? is it possible to do it?

    thanks in advance,

    Jorge

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Yes just change the tree type in the SQL create statement.

    USING HASH(tempvar1)
    USING RTREE(tempvar1)
    USING BTREE(tempvar1)

    I'm not sure if MySQL has plans for other data structure in the future, or you can "compile" an alternative into it, but the ones i've listed here are the available ones as of 5.0.27-community version.

  6. #6
    Join Date
    Nov 2007
    Posts
    3
    Quote Originally Posted by aschk

    I'm not sure if MySQL has plans for other data structure in the future, or you can "compile" an alternative into it, but the ones i've listed here are the available ones as of 5.0.27-community version.
    That is exactly what i am looking for. I want to try an alternative but i don't have a clue about how to do it.. do you know some site that has some relevant info?

    thanks,

    Jorge

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    wikipedia, google, mysql.net
    All of the above will assist you in learning how these structures are formed.

    Are you trying to create your own? I would advise against that (unless your MIT or Harvard material), and just use proven structures. The ones I listed are available to MySQL. In reality for the "user", just use the default.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •