Optimize MySQL database query

Question

I had a commenting application in my web site. The comments will store in a MySQL table . table structure as follows

id   |  Comment    |  user   |   created_date
------------------------------------------------------
12   |  comment he |  1245   |   2012-03-30 12:15:00
------------------------------------------------------

I need to run a query for listing all the comments after a specific time. ie .. a query like this

SELECT * FROM comments WHERE created_date > "2012-03-29 12:15:00" ORDER BY created_date DESC

Its working fine.. My question is if I got a 1-2 lakh entry in this table is this query is sufficient for the purpose ? or this query will take time to execute ? In most cases I have to show last 2 days data + periodically ( interval of 10 mins ) checking for updates with ajax from this table ...

Please help Thanks

currently I am using CURRENT_TIMESTAMP in created_date field — ramesh
– ramesh, Commented Mar 28, 2012 at 5:31

Ashwin A · Accepted Answer · 2012-03-28 10:00:50Z

3

Place index in created_date field. It will boost performance a lot...

Links: MySQL: http://dev.mysql.com/doc/refman/5.0/en/create-index.html
Using SQLyog: http://www.weberdev.com/ViewArticle/Managing-Foreign-Key-Relationships-In-MySQL-Using-SQLyog
WIKI: http://wiki.phpmyadmin.net/pma/primary_key

edited Mar 28, 2012 at 10:00

Ashwin A

3,83425 silver badges28 bronze badges

answered Mar 28, 2012 at 5:22

KoolKabin

17.8k37 gold badges113 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ramesh Over a year ago

can you please explain .. how to create a index ?

CoreCoder · Accepted Answer · 2012-03-28 05:27:48Z

2

You should add an index on "created_date" if not yet. more over you can use "explain" to optimize your query. here is some easy ways to optimize queries.

answered Mar 28, 2012 at 5:27

CoreCoder

4191 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

Optimize MySQL database query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related