Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

1 Answer 1

up vote 8 down vote accepted

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.

share|improve this answer
    
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
    
@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
    
Very helpfull, thanks a lot! –  Mariano Argañaraz Nov 21 '12 at 17:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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