Sandbox

SELECT 
  cscart_discussion_posts.*, 
  cscart_discussion_messages.message, 
  cscart_discussion_rating.rating_value 
FROM 
  cscart_discussion_posts 
  LEFT JOIN cscart_discussion_messages ON cscart_discussion_messages.post_id = cscart_discussion_posts.post_id 
  LEFT JOIN cscart_discussion_rating ON cscart_discussion_rating.post_id = cscart_discussion_posts.post_id 
WHERE 
  cscart_discussion_posts.thread_id IN (47348) 
  AND cscart_discussion_posts.status = 'A' 
ORDER BY 
  cscart_discussion_posts.timestamp DESC 
LIMIT 
  0, 10

Query time 0.00067

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cscart_discussion_posts.`timestamp` desc",
        "table": {
          "table_name": "cscart_discussion_posts",
          "access_type": "ref",
          "possible_keys": ["thread_id", "thread_id_2"],
          "key": "thread_id_2",
          "key_length": "6",
          "used_key_parts": ["thread_id", "status"],
          "ref": ["const", "const"],
          "rows": 1,
          "filtered": 100,
          "index_condition": "cscart_discussion_posts.`status` = 'A'",
          "attached_condition": "cscart_discussion_posts.thread_id <=> 47348 and cscart_discussion_posts.`status` <=> 'A'"
        }
      }
    },
    "table": {
      "table_name": "cscart_discussion_messages",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["post_id"],
      "ref": ["cscart_migrate.cscart_discussion_posts.post_id"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_discussion_rating",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["post_id"],
      "ref": ["cscart_migrate.cscart_discussion_posts.post_id"],
      "rows": 1,
      "filtered": 100
    }
  }
}

Result

post_id	thread_id	name	timestamp	user_id	ip_address	status	email	message	rating_value
108978	47348	Gavin S.	1430167020	0	24.199.168.30	A		Although these sconces are very expensive, I have yet to find anything like them elsewhere in the market. We've had ours up for the last two months and have really enjoyed them. It would have been nice if the bulb came with the sconce (the edison bulbs we have in there now get really hot...is that normal?). As a result of the heat from the bulbs we are planning to flip the sconce arms to point upward, just trying to figure out how that works (it's not detailed in the instructions but it is shown in several sample photos so surely it can be done). Anyway, all that to say, not 100% perfect, but pretty darn close. I'd give them a solid A. And they are totally coming with us if we ever move...	4