Need advise of tuning mysql performance for fast transaction time

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

tune each query individually using EXPLAIN, rather than globbing them all together in a mass stress test, Maybe its one query performing badly because it has no suitable indices

also consider replacing ‘select *’ with ‘select field1, field3 etc’. In most cases you dont need every single field returning, and
this will often reduce network traffic