mysql inner join statement and memory overuse !

Question

hey guys

im running bellow query to fetch from 3 mysql tables and it works fine but it increases memory usage and sometime it takes 10 seconds to start loading the page

  INNER JOIN table_tags AS ntg ON (ns.tags = '' OR CONCAT(' ',COALESCE(ns.tags,' '),' ') LIKE CONCAT('% ',ntg.tid,' %'))
  INNER JOIN table_topics AS nto ON (ns.associated = '' OR CONCAT(' ',COALESCE(ns.associated,'-'),' ') LIKE CONCAT('%',nto.topicid,'%' ))

problem is in Inner Join statemnet and if i remove

ns.tags = '' OR

and

ns.associated = '' OR

the memory overuse problem will be fixed but cant show stories with empty tag field

is there any other way to write bellow statement to include all stories even those with no tags !?

  INNER JOIN table_tags AS ntg ON (ns.tags = '' OR CONCAT(' ',COALESCE(ns.tags,' '),' ') LIKE CONCAT('% ',ntg.tid,' %'))

my tags id are stored in table_ns like ( 14 17 2 ) seprated with space

Answer 1

Your condition ns.tags = '' OR ... means that if ns.tags = '' is true this record from table_stories will be joined with all the records in table_tags. Same with ns.associated = '' OR ... This can easily "create" huuuge result sets and that's most likely not what you want.
If you really don't want/can't change/normalize the table structure (as you've stated in a comment to my previous answer) my best guess is to use two LEFT JOINs like:

SELECT
  ns.*,
  ntg.tid,
  nto.topicid,
  group_concat(DISTINCT ntg.tag ) as mytags,
  group_concat(DISTINCT ntg.slug ) as tagslug,
  group_concat(DISTINCT nto.topicname ) as mytopics,
  group_concat(DISTINCT nto.slug ) as topicslug,
  ns.counter AS counter_num
FROM
  table_stories AS ns
LEFT JOIN
  table_tags AS ntg
ON
  CONCAT(' ', ns.tags,' ') LIKE CONCAT('% ',ntg.tid,' %')
LEFT JOIN
  table_topics AS nto
ON
  CONCAT(' ', ns.associated, ' ') LIKE CONCAT('%',nto.topicid,'%' )
WHERE
  time <= '4711'
GROUP BY
  ns.sid
ORDER BY
  ns.sid DESC,ns.hotnews DESC

But MySQL can't use indices for this, so the query will result in full table scans. It will also need temporary tables and filesort, i.e. the query is relatively slow.
Without changing the table structure but the format of the data from 1 2 3 to 1,2,3 you can at least get rid of Concat() and LIKE by using the slightly faster FIND_IN_SET(str, strlist).

---

Just for completeness and in case you change your mind: http://en.wikipedia.org/wiki/Database_normalization