Good run time in mysql 5.0 without index but not in 5.1

I have a database created by MySQL 5.0.

Tables have not index except primary key field.

I have a query and run time is 0.9 s in MySQL 5.0. when i export database and import to newer MySql version like 5.1 (i have tested 5.5 too) with same hardware run time query is 120 s.

if i index fileds on join i get a 0.2 run time.

my question is then why i have a good run time in MySQL 5.0 without index?

what is differen开发者_Python百科ce between 5.0 and 5.1 (5.5)?

The question is really

"Do I need an index, or don't I?"

The only way to answer this question is to know your application, and profile your database queries.

In general, if performance is OK, if a table is reasonably small, and if your queries (and, more importantly, your joins) aren't particularly "selective", then typically you DON'T necessarily want to create an index.

Here's a good summary of "selectivity" (and some good advice on indexing in general):

http://www.bennadel.com/blog/1423-Rick-Osborne-On-Database-Indexing-And-SQL-Best-Practices.htm

Selectivity in a nutshell: Your data is a card catalog in a library. (Remember those things?) Or a filing cabinet full of manila folders. An index is the tabs on the cards or folders that helps you find what you are looking for. Selectivity is the ratio of unique keys to the total number of rows.

Remember, too, that there are LOTS of variables that determine "performance". "DB Version" and "indexing" are just two of many.

I would like to comment on paulsm4, but I don't have enough privileges (yet). Your answer is not so smart, for this reason :

The question is not : "Do I need an index, or don't I?"

but?

Why when I got an no index, no cache , and the same data, 5.1 (and 5.5) is a hundreds of time slower than 5.0 ? -> No idea...

and

What are the differences between 5.0 and 5.1 ? -> Check the mysql official changelog

Next time I see a homeless in the street, asking me one $, I'll answer him really : The question is not "can I have one $ (please)?" but "How can I become multimillionaire quickly?"

and then...I'll continue my way spending my $ on a random cheap soda can.