Sandbox

SELECT 
  CONCAT(u.firstname, ' ', u.lastname) as author, 
  b.page_id 
FROM 
  cscart_blog_authors as b 
  LEFT JOIN cscart_users as u ON b.user_id = u.user_id 
WHERE 
  b.page_id IN (7, 8, 10, 9)

Query time 0.00037

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	index	PRIMARY	PRIMARY	6		4	Using where; Using index
1	SIMPLE	u	eq_ref	PRIMARY	PRIMARY	3	hobilifedt.b.user_id	1

Result

author	page_id
Admin Admin	7
Admin Admin	8
Admin Admin	9
Admin Admin	10