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

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.

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.

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

thanks in advance,

Jorge

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.

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

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.