join lots of tables, optimisation problems

[BB101]

I have a complex query that takes approx 1 second to run; this however, is not acceptable. Does anyone have any good resource sites or recommendations on how to optimised this query? I have indexes on all fields used in the joins/wheres/groups but I cant seem to get this below 1 second.

Thanks in advance


--BB

 

[BB101]

After playing with the query (removing bits) to see what was slowing it down, I discovered when I remove the group by the query runs in 0.1 seconds (acceptable)... Does anyone have any ideas how I can get this to go faster with the group by?

--BB

 

[TonyGroves]

If you post your table structures and query code, somebody might notice something.

-----
ALTER world DROP injustice, ADD peace;

 

[BB101]

Current code to get stuff out, time to run 0.0017 sec

SELECT a.*, t.town, e.type, e.unique_id, e.firstname, e.surname, e.expertise, e.trading_name
        FROM qa_uk a
          LEFT JOIN ayn_experts e
            ON a.user_id = e.unique_ID
              AND e.active = 1
          LEFT JOIN ayn_towns t
            ON t.id = a.town_id
          WHERE a.q_id = '53840'
            AND a.active = 1
          ORDER BY a.datetime

The desired output, time to run: 7.8004 sec

SELECT a.*, t.town, e.type, e.unique_id, e.firstname, e.surname, e.expertise, e.trading_name, COUNT(DISTINCT ql.id) + COUNT(DISTINCT qu.q) + COUNT(DISTINCT al.id) + COUNT(DISTINCT au.id) as posts
        FROM qa_uk a
          LEFT JOIN ayn_experts e
            ON a.user_id = e.unique_ID
              AND e.active = 1
          LEFT JOIN ayn_towns t
            ON t.id = a.town_id
          LEFT JOIN qq_local ql
            ON ql.user_id = a.user_id
            AND ql.active = 1
          LEFT JOIN qa_local al
            ON al.user_id = a.user_id
            AND al.active = 1
          LEFT JOIN qq_uk qu
            ON qu.user_id = a.user_id
            AND qu.active = 1
          LEFT JOIN qa_uk au
            ON au.user_id = a.user_id
            AND au.active = 1
          WHERE a.q_id = '53840'
            AND a.active = 1
          GROUP BY a.q_id
          ORDER BY a.datetime

Data structures are as follows:

CREATE TABLE `ayn_towns` (
  `id` int(11) NOT NULL auto_increment,
  `town` varchar(128) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `town` (`town`),
  KEY `area` (`area`)
) TYPE=MyISAM AUTO_INCREMENT=1;

CREATE TABLE `qa_local` (
  `id` int(11) NOT NULL auto_increment,
  `q_id` int(11) NOT NULL default '0',
  `town_id` int(11) NOT NULL default '0',
  `user_id` int(11) default NULL,
  `datetime` int(11) NOT NULL default '0',
  `name` varchar(64) NOT NULL default '',
  `text` text NOT NULL,
  `active` int(11) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `town_id` (`town_id`),
  KEY `user_id` (`user_id`),
  KEY `active` (`active`),
  KEY `q_id` (`q_id`),
  KEY `datetime` (`datetime`)
) TYPE=MyISAM AUTO_INCREMENT=3788 ;

CREATE TABLE `qa_uk` (
  `id` int(11) NOT NULL auto_increment,
  `q_id` int(11) NOT NULL default '0',
  `town_id` int(11) NOT NULL default '0',
  `user_id` int(11) default NULL,
  `datetime` int(11) NOT NULL default '0',
  `name` varchar(64) NOT NULL default '',
  `host` varchar(255) default NULL,
  `text` text NOT NULL,
  `active` int(11) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `q_id` (`q_id`),
  KEY `town_id` (`town_id`),
  KEY `user_id` (`user_id`),
  KEY `active` (`active`),
  KEY `datetime` (`datetime`)
) TYPE=MyISAM AUTO_INCREMENT=7530 ;

CREATE TABLE `qq_local` (
  `id` int(11) NOT NULL auto_increment,
  `q` int(11) NOT NULL default '0',
  `q_town_id` int(11) NOT NULL default '0',
  `datetime` int(11) NOT NULL default '0',
  `user_id` int(11) default NULL,
  `town_id` int(11) NOT NULL default '0',
  `name` varchar(64) NOT NULL default '',
  `text` text NOT NULL,
  `active` int(11) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `q` (`q`),
  KEY `q_town_id` (`q_town_id`),
  KEY `user_id` (`user_id`),
  KEY `town_id` (`town_id`),
  KEY `active` (`active`),
  KEY `datetime` (`datetime`),
) TYPE=MyISAM AUTO_INCREMENT=51558 ;

CREATE TABLE `qq_uk` (
  `q` int(11) NOT NULL default '0',
  `datetime` int(11) NOT NULL default '0',
  `user_id` int(11) default NULL,
  `town_id` int(11) NOT NULL default '0',
  `name` varchar(64) NOT NULL default '',
  `text` text NOT NULL,
  `del_date` int(11) default NULL,
  `active` int(11) NOT NULL default '1',
  KEY `q` (`q`),
  KEY `datetime` (`datetime`),
  KEY `user_id` (`user_id`),
  KEY `town_id` (`town_id`),
  KEY `active` (`active`)
) TYPE=MyISAM;

Thanks too all in advance for reading this far!

--BB

 

[TonyGroves]

I notice you're joining table qa_uk to itself on user_id=user_id, which is redundant processing. If you correct that, it might improve the speed.

Also, you didn't include details of the ayn_experts table, so there could be something there too.


-----
ALTER world DROP injustice, ADD peace;

 

[BB101]

not much point putting the experts table on as it only the expert_id which is ever used, and that is indexed.

The reason im joining qa_uk to itself is so that I can read how many other posts there were by that person. I cannot see another way of doing this, other than with joins and/or some cache system.

--BB

 

[TonyGroves]

Sorry, my mistake. Your qa_uk/qa_uk join is not redundant.

Back to the drawing-board.

-----
ALTER world DROP injustice, ADD peace;