-1

thanks for reading this question i hope someone can give some insight into some steps.

Here is my steps report.

I added memcached instead of mysql cache. optimize the 2 tables from buddypress that where making the mysql overload. But still the problem is there.

Not sure what the step might be next? Any guide into this would be much appreciated

Table 1 structure < has 16,272 rows innoDB type size 109.2 MiB
id - bigint(20) null =no auto increment
creator_id bigint(20)
name varchar(100)
slug varchar(200)
description longtext
status varchar(10)
enable_forum tinyint(1)

date_created datetime

table 2 < 30,588 rows innoDB 10.1 MiB

id bigint(20)

group_id bigint(20)

meta_key varchar(255)

meta_value longtext

indexes are set to all.

Test Query... MORE INFO: Executed 21 min ago for 3 sec on Database --> wp. SELECT DISTINCT g.id, g.*, gm1.meta_value AS total_member_count, gm2.meta_value AS last_activity FROM b9t_bp_groups_groupmeta gm1, b9t_bp_groups_groupmeta gm2, b9t_bp_groups g WHERE g.id = gm1.group_id AND g.id = gm2.group_id AND gm2.meta_key = 'last_activity' AND gm1.meta_key = 'total_member_count' AND g.status != 'hidden' ORDER BY last_activity DESC LIMIT 0, 20

Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.5.33-31.1-log - Percona Server (GPL), Release rel31.1, Revision 566 Protocol version: 10 cpsrvd 11.42.1.16 Database client version: libmysql - 5.0.96 PHP extension: mysqli

bp_groups InnoDB 10 Compact 15191 7703 117030912 0 6979584 5242880 14555 2014-06-12 11:39:33 NULL NULL utf8_general_ci NULL

bp_groups_groupmeta InnoDB 10 Compact 30487 86 2637824 0 7946240 4194304 32376 2014-06-12 11:59:52 NULL NULL utf8_general_ci NULL

  • What query(ies) are you running? As the other poster said, DDL would be good (also sample data - SQLFiddle?). Server version and OS always good. – Vérace Jun 12 '14 at 22:42
  • added some more info hope that is good – EVX Jun 13 '14 at 1:46
0

InnoDB table should have a Primary Key defined, or else it will try to find a unique combination of fields to create one. The best is to explicitly add an Auto_increment Integer Unsigned ID.

SHOW CREATE TABLE table_name;
SHOW TABLE STATUS table_name;

Will be helpful

  • The both 2 tables have id auto_incre~ and primary set on it. (Forgot to include that sorry).. – EVX Jun 13 '14 at 1:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.