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 (10, 9, 8)

Query time 0.00083

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

Result

author page_id
Admin Admin 8
Admin Admin 9
Admin Admin 10