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. – Mariano Argañaraz 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? – Mariano Argañaraz 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.