How to enhance mysql performance? 1

[spookie]

Hello,

I have written a script for geneating reports for the order processing.
The report is generated using 3-4 tables.Even though the record in each table is not more than 5000, it takes a lot of time generating it.
Wht could be the reasons behind that and how can i enhance the performace of data retrieval, besides using indexes?

Thanks in advance

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[abombss]

Always run a describe statement on your sql and see what indexes your query is using. If you query is not using good indexes, run an optimize on the tables and see if that helps. Otherwise create new indexes, and or post some code so we can help you tweak it.

MySQL is very fast, espicially with only a couple thousand records in the tables. My guess is you have a bad join or are not using indexes.

abombss

 

[spookie]

Thanks for the reply abombss!!

Like u said i have added some indexes and the query performances have really improved significantly.
for query like:
select * from tablename where name = '$name' ;
i have created index for columns id,name where id is auto incremented primary key.

Realised thhe power of indexes, how it can make a difference.

Any thoughts are welcome



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[abombss]

Glad to see it helped.

Remember if in doubt about perfermance run an explain [query], (not a describe statement...) which will show the query plan and indexes being used.

If you made a significant amount of inserts, updates, or deletes you may need to run an optimize [table_name] on the tables to refresh the indexes.

Used properly indexes will speed up selects dramatically, but they will slow down inserts, delets, and updates.

The MySQL documentation is your friend, there are some great documents about indexes and query plans.

Another note... Try and avoid the *, even if you need every column, it actually slows down query performance and has potential to break your application if columns are added, removed or the order is changed.

Good Luck,

abombss

 

[spookie]

Great,Thanks for the tips!!

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.