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}
  LIMIT 12;

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)


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.

  • yes, I laread have an index to table(id), I'll add these index, that are clever indeed, I addition I was wwondering if the query is a good query itself (with the UNIX_TIMESTAMP(t.created_at) > {$today} and everything). Thank I'll add this indexes. Nov 21 '12 at 16:55
  • Sorry, I missed that you are using UNIX_TIMESTAMP; mysql doesn't support function based indexes, so I'd recommend to compare t.created_at with datetime (avoiding UNIX_TIMESTAMP function) or change type of t.created_at.
    – a1ex07
    Nov 21 '12 at 16:59
  • It is faster now that I have added those indexes, but in adition I see I can query like this: SELECT * FROM tweets WHERE created_at > '2012-11-20 00:00:00' So it would be the same as using UNIX_TIMESTAMP()... right? Nov 21 '12 at 17:19
  • 1
    @Mariano Argañaraz : Problem with function in mysql that it doesn't use index: WHERE UNIX_TIMESTAMP(field) > some_value -- doesn't use index on field (it may use index scan in case of covering index, but not index seek or index range scan ); WHERE field > some_value does use index on field properly... Some other RDMS(Oracle for instance) let you create function based index , but not mysql
    – a1ex07
    Nov 21 '12 at 17:25
  • @Mariano Argañaraz: If you change the ` WHERE UNIX_TIMESTAMP(t.created_at) > {$today}` to ` WHERE field > FROM_UNIXTIME({$today})` the index on (t.created_at) will be used properly. I'm updating my answer...
    – a1ex07
    Nov 21 '12 at 17:28

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .