How can I improve this DB query? it takes too much time

Question

The query is this:

$today = $date->getTimestamp();

SELECT t.*, u.screen_name, u.profile_image_url
  FROM table AS t 
  LEFT JOIN users AS u 
  ON t.user_id=u.id_str 
  WHERE UNIX_TIMESTAMP(t.created_at) > {$today}
  ORDER BY id DESC 
  LIMIT 12;

Answer 1

Even though you didn't post table definitions in your question so I can't tell for sure what is missed, the following indexes will be beneficial :

1. Index on table (user_id, created_at)
2. Index on users(id_str) or even users(id_str,screen_name,profile_image_url)
3. I guess you already have an index on table(id)

UPDATE

Change WHERE : from WHERE UNIX_TIMESTAMP(t.created_at) > {$today} to WHERE t.created_at > FROM_UNIXTIME({$today}), this way optimizer will use index that includes create_at column more efficiently.