Some of the challenges we get! Have I correctly found the difference between the two queries?...
SELECT DISTINCT a.id as key1, u.id as key2,
cc.id as key3, a.title, a.sectionid,
a.catid, a.created,
CASE YEAR(a.modified) WHEN 0 THEN a.created ELSE a.modified END As modified,
a.created_by, a.created_by_alias,
u.name AS author, a.hits,
CASE WHEN CHAR_LENGTH(a.alias)
THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END AS slug,
CASE WHEN CHAR_LENGTH(cc.alias)
THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END AS catslug ,
"-" AS voting
FROM jos_content AS a
LEFT JOIN jos_jf_content AS jf ON jf.reference_id = a.id
LEFT JOIN jos_users AS u ON u.id = a.created_by
LEFT JOIN jos_categories AS cc ON a.catid = cc.id
WHERE 1
AND a.sectionid = 2
AND a.access <= 0
AND (a.state = 1)
AND ( a.publish_up = '0000-00-00 00:00:00'
OR a.publish_up <= '2010-02-09 01:35:52' )
AND ( a.publish_down = '0000-00-00 00:00:00'
OR a.publish_down >= '2010-02-09 01:35:52' )
# Either these:
AND LOWER( a.title ) LIKE '%waste%'
OR ( ( LOWER( a.introtext ) LIKE '%waste%' )
OR ( LOWER( a.fulltext ) LIKE '%waste%' )
OR ( LOWER( a.metakey ) LIKE '%waste%'))
# Or this:
AND MATCH (a.title,a.introtext,a.fulltext,
a.metakey) AGAINST(' "waste" ' IN BOOLEAN MODE)
* OR, LOWER, and leading '%' all bypass any use of index --> slow
* FULLTEXT, when present, will trump all other indexes.
* 'foowaste' will match '%waste%' but will not match in fulltext. This probably explains the mismatch in counts.
If you want further discussion of speed, please provide SHOW CREATE TABLE and EXPLAIN.