MySQL, Indexing and Optimization

[bachx]

Hello, I've noticed my scripts are becoming more and more slower as more users are coming to my site. I've pointed the problem to my queries, but I some help in order to optimize them.

 

All my tables are indexed (By an auto-incremented primary key called 'ID'), but it seems that I wasn't using those indexes at all. For example, most of my SELECT statements are like this:

SELECT * FROM users WHERE name='xxx'

 

Does the above query scan through every row, or will it return the result directly? If not, I seriously need to change that. So, will the below query resolve my problem? (By selecting the data using the ID not the Name)

 

SELECT * FROM users WHERE ID='xxx'

 

Also, does the same rule applies to the UPDATE query statements, or just the SELECT ones? And what other optimizations/tips you recommend in general for my PHP/MySQL scripts? Thanks.

 

 

[Jessica]

You can make the name column an index, or you can search by the auto-incrementing field.

[bachx]

The name as an index? I thought a table index should be a numeric value.

[lightningstrike]

Yes the Index should generally be a integer as it is faster and less space consuming but in your case it will only benefit you.

[bachx]

I've just changed the 'Names' field into an Index Key, things seems to be faster now, I guess it's not scanning 5000 rows to return one result anymore.

 

Anymore tips regarding mysql optimizations?

[lightningstrike]

Yes the INDEX will speed up any statement that has a WHERE clause searching the name.

 

It might be better practice to instead use WHERE ID = 'xxx', if your index is using to much space I believe MySQL can create partial indexes from a shell command as well.

 

Avoid using SELECT * FROM table.

 

* means your retrieving all the data from the table where the conditions are met. Instead SELECT field1,field2 FROM table would consume less memory and time then retrieving unecessary fields you don't need.