Jump to content

Add indexes to database


dpsd

Recommended Posts

I've contacted IPS for that and they told me that the database structure is what the developers consider optimal and no other changes are recommended.
However people here suggested adding indexes to speed up some queries.

My slow-log is around 10MB so I uploaded it on my server.
www.wehellas.gr/log-slow-queries.log

It's a joomla + IPB database (bridged), any suggestions on what to change at least for the IPB part?
Thanks

Link to comment
Share on other sites

OK here is a small part of the log, the first one includes old logs as well so it's of no use, I deleted it from the server.
Query time on all of these is very long...

# User@Host: wehellas_wehella[wehellas_wehella] @ localhost []

# Query_time: 20  Lock_time: 0  Rows_sent: 1  Rows_examined: 285584

SELECT g.* FROM v3_core_acl_aro_groups AS g INNER JOIN v3_core_acl_groups_aro_map AS gm ON gm.group_id = g.id INNER JOIN v3_core_acl_aro AS ao ON ao.id = gm.aro_id WHERE ao.value='72389' ORDER BY g.id;

# Time: 100919 17:12:31

# User@Host: wehellas_wehella[wehellas_wehella] @ localhost []

# Query_time: 30  Lock_time: 1  Rows_sent: 20  Rows_examined: 176205

SELECT  m.member_id FROM ibf_members m  LEFT JOIN ibf_pfields_content p ON ( p.member_id=m.member_id ) 

 LEFT JOIN ibf_profile_portal pp ON ( pp.pp_member_id=m.member_id )   WHERE m.member_group_id=3 AND m.member_group_id NOT IN(1) ORDER BY m.members_l_display_name asc LIMIT 40940,20;

# Time: 100919 17:18:28

# User@Host: wehellas_wehella[wehellas_wehella] @ localhost []

# Query_time: 12  Lock_time: 0  Rows_sent: 20  Rows_examined: 182661

SELECT  m.member_id FROM ibf_members m  LEFT JOIN ibf_pfields_content p ON ( p.member_id=m.member_id ) 

 LEFT JOIN ibf_profile_portal pp ON ( pp.pp_member_id=m.member_id )   WHERE m.member_group_id NOT IN(1) ORDER BY m.members_l_display_name asc LIMIT 43800,20;

# Time: 100919 17:21:51

# User@Host: wehellas_wehella[wehellas_wehella] @ localhost []

# Query_time: 11  Lock_time: 0  Rows_sent: 20  Rows_examined: 157851

SELECT  m.member_id FROM ibf_members m  LEFT JOIN ibf_pfields_content p ON ( p.member_id=m.member_id ) 

Link to comment
Share on other sites

Looking at a 3.1.2 database now, the last query does use indexes, so I'm not sure why that would be a slow query.

Second query doesn't use the mgroup index on the members table, thus 'Using where; Using filesort'

Third query use indexes, although the select on the members table are 'Using where; Using filesort'


So there are not any indexes you can add for those.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...