MySQL Performance: MySQL => Need advise of tuning mysql performance for fast transaction time

Need advise of tuning mysql performance for fast transaction time [message #494]

Thu, 21 December 2006 01:47

genux33
Messages: 1
Registered: December 2006

Junior Member

Hi,

I wrote a php script to perform load testing as following

- select * from users where user_id = 'xxxxx' //xxxxsx is randomly generated, simulating user logging in
- select pets.* from pets INNER JOIN users ON pet_owner_id='xxxxx' //load user's pets
- select messages.* from messages INNER JOIN users ON message_user_id='xxxxx' //load user's messages
- select friends.* from friends INNER JOIN users on friends_id='xxxxx' //load user's friends
- select testimonials.* from testimonials INNER JOIN testimonials on testimonial_user_id='xxxxx' //load user's testimonials
- select guides.* from guides .......... //load latest uploaded guides
- select events.* from events ....... //load latest events

Database size as following

No Database Table Number of records
1 countries 239
2 Events 20,8212
3 Friends 53,5935
4 Guides 59,388
5 Hobbies 10
6 Messages 514,488
7 Mstatus 5
8 Pets 10,715,973
9 Purpose 5
10 Testimony 200,000
11 User_to_hobbies 5,446,651
12 users 1,019,994

When i execute the script manually from browser, time taken to load the page is 1.3 sec - 1.9sec

As i did a load of 100 concurrent users, the longest transaction time is more than 50sec..

Is that any way to reduce the transaction time taken?

Regards,
Zhiwei

Report message to a moderator