optimizing a structure?

[Zoice]

Hello there,
I am trying to optimize a bad and outdated structure but my new big query takes a long time to run because the bnn_headlines has 7 million rows. Is there any way to optimize this query a bit more?

SELECT
`a`.`headline_id`,
`a`.`name`,
`a`.`title`,
`a`.`url`,
`a`.`text`,
`a`.`htimej`,
`b`.`sgid`
FROM
`news`.`bnn_headlines` AS `a`
INNER JOIN
`news`.`headline_category` AS `b`
ON
`a`.`headline_id` = `b`.`headline_id`
INNER JOIN
`news`.`categories_index` AS `c`
ON
`b`.`category_id` = `c`.`category_id`
WHERE
`c`.`cat_id` = '$cat_id'
AND
`a`.`htimej` > $time
ORDER BY
`a`.`htimej` DESC
LIMIT
0, $limit

Here's the structure of the 2 tables:

mysql> explain bnn_headlines;
+-------------+------------------+------+-----+---------+----------------+
-- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[inder]

In your select statement, there are three tables, but categories_index is only used to find category_id by cat_id. My advice for you is to try to do that in a separate step:
SELECT category_id
FROM news.categories_index
WHERE cat_id = '$cat_id'

and then simplify step two:

SELECT a.headline_id,a.name,a.title,a.url,
a.text, a.htimej, b.sgid
FROM news.bnn_headlines AS a
INNER JOIN news.headline_category AS b
ON a.headline_id = b.headline_id
WHERE b.category_id = '$category_id'
AND a.htimej > $time
ORDER BY a.htimej DESC
LIMIT 0, $limit

This will work if there is only one match in news.categories_index.
An index starting with headline_category.category_id would probably help. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --