MySQL complex query optimisation

[BB101]

I have a query which is taking 10 seconds to run with a relatively empty database. This is acceptable, but I expect it to take longer when there is more data.

Dump of table structure:

CREATE TABLE towns (
  id int(11) NOT NULL auto_increment,
  town varchar(128) NOT NULL default '',
  area varchar(128) NOT NULL default '',
  gn decimal(6,2) NOT NULL default '0.00',
  la decimal(6,2) NOT NULL default '0.00',
  PRIMARY KEY  (id),
  KEY town (town),
  KEY area (area)
) TYPE=MyISAM AUTO_INCREMENT=1;

CREATE TABLE m_posts (
  id int(11) NOT NULL auto_increment,
  category_id int(11) NOT NULL default '0',
  all tinyint(4) NOT NULL default '1',
  max int(11) default NULL,
  text text NOT NULL,
  PRIMARY KEY  (id),
  KEY category_id (category_id),
  KEY all (all),
  KEY max (max)
) TYPE=MyISAM AUTO_INCREMENT=10 ;

CREATE TABLE mlk_posttown (
  post_id int(11) NOT NULL default '0',
  town_id int(11) NOT NULL default '0',
  switch tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (post_id,town_id),
  KEY post_id (post_id),
  KEY town_id (town_id),
  KEY switch (switch)
) TYPE=MyISAM;

The query is as follows:

SELECT p.id as post_id,
  t.id as town_id,
  count(ptc.post_id) as posted,
  p.max
FROM towns t, m_posts p
  LEFT JOIN mlk_posttown pt ON
    p.id = pt.post_id AND t.id = pt.town_id
  LEFT JOIN mlk_posttown ptc ON
    p.id = ptc.post_id AND ptc.switch = 0
  WHERE (pt.switch = 1 OR
        (pt.switch is null AND p.all = 1))
  GROUP BY t.id, p.id
  HAVING max > posted

This query would take 10 seconds to run and ordinarily return 6000-10000 rows.

Basically, I want to get a load of results back where the post and town switch is not 0 (i.e already been posted), if p.all is 0 then the post is only to get into towns with the switch flag set to 1 in the posttown link table. Also, no post should go to more towns than the max field in the posts table states. With this in mind, I would like either
a) a fast query i can run every few seconds without too much hassle
b) a slower query that I can cache and update once an hour (or whenever)

It's taken me painfully long to get this far, and on the way I made a one interesting observation. Text fields REALLY slow things down (in this situation anyway); selecting the text field in with the results added 70 seconds to the query run time, (yes, from 10-14 seconds run to to 80-90 seconds!).

Here is an "explain" of the select:

table  type    possible_keys            key      key_len  ref       rows  Extra  
t      index   NULL                     PRIMARY  4        NULL      1131  Using index; Using temporary; Using filesort 
p      ALL     all                      NULL     NULL     NULL      9   
pt     eq_ref  PRIMARY,post_id,town_id  PRIMARY  8        p.id,t.id 1     Using where 
ptc    ref     PRIMARY,post_id,switch   PRIMARY  4        p.id      18

I've tried my hardest to include every detail, please feel free to ask me questions. I don't know of any way to make this run any faster, does anyone else?

--BB

 

[TonyGroves]

I haven't examined your problem in detail (life's too short), but here's a few observations:

- Your query joins the towns and m_posts tables without any field matching condition. That's going to take a long time and eat up memory. You could try reordering the sequence of your joins to make it more efficient.

- Your tables use VARCHAR instead of CHAR fields. That's normally slower, though uses less disc space.

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

 

[BB101]

Thanks for the reply. Doing a cartisian join on m_posts and towns seemed to be the only way I could get the data I wanted. If you know of a better way I'm all open to suggestions (even total restructuring).

--BB