indexing

[canabatz]

Hello ,i tryed to search for this topic ,and i didn't find!

im new to optimizing mysql by indexing ,and i want to know if it is good?

i got this table with users registration details , i got username, first_name, last_name, and some more !

my question is :

is it good to index all this 3 fields ?

thank's

[RedSon]

Are you looking for a theoretical answer or do you want to know if mysql is efficient in handling multiple indexes on a table?

[TheServant]

I think that indexing is good if you are doing a lot of searches for that column. I don't think you should be indexing all of those, even if you have a search or regular call to them, unless your queries are getting slow. I wouldn't even think about it until you have 10,000 records, and even then, you might not notice it until 100,000 records. Then best way to organise this is to run a timer script. Make 10,000 dummy records in a table, run a SELECT * WHERE first_name="test" query a few times to get an average for unindexed, and then index the column, and try a few time to get an average for indexed.

If you do this, please post your results and findings as it could be helpful to others. But in short, if you're not doing very populated tables, and regular searches, then don't worry about it. I would always be trying to optimize, and maybe indexing will speed it up a little, and you should run some tests to determine that. However, indexing uses memory, and could be a waste if you don't notice any difference in query time.

[canabatz]

ok ,i got it!!

one more question :)

i want to understand what query will be faster:

if i do:

SELECT * FROM 'my_table' where id=$id;

and from my_table i only need those columns first_name , last_name, user_name and there is much other columns in the table that i dont need.


or i do like :

SELECT first_name, last_name, user_name FROM `my_table` where id=$id

thanx!!

[RedSon]

They should be exactly the same because you are pulling the same data set just filtering it. But you should use the analyze functions in your SQL database engine to see how long each takes. That is the purpose of the analyze functions, to see how long things take and optimize from there.

[mwasif]

There will be no performance difference if you are not querying the database frequently. But if you performing queries very frequently then you must get data for only those columns which you need.

[canabatz]

ok ,thanx :)

one more question :)

i got my database as default MyISAM !

i got some time slow inserts and updates ,there can be like 5 inserts every second
i read that InoDB is better in insert delete and update!

are you recomend to move to InoDB? if i got allot of insert ,update?

thanx