CodingForums.com - View Single Post

Old Pedant · 11-20-2012, 10:26 PM

I will say that if, for example, you wanted to search for (say) the word "people" in the TEXT of all 20,000 tweets, there is no good way to use an INDEX for that with MySQL. So, yes, MySQL would have to actually read all 20,000 records and look in the tweet text field to try to find that word. That is:

Code:

SELECT * FROM tweets WHERE tweet LIKE '%people%'

will not be particularly fast.

BUT...

But say you put an index on the WHENTWEETED field and you knew that the tweet you are looking for was tweeted in the last two days:

Code:

SELECT * FROM tweets WHERE tweet LIKE '%people%' AND whenTweeted > DATE_SUB( NOW(), INTERVAL 2 DAY )

Now MySQL will only need to look at the full test of the tweet in records added in the last 2 days. The selection to find all records in the last 2 days will take perhaps 5 milliseconds and then the time to search the test will depend on how many records were posted in those 2 days.

In other word, take advantage of your indexes! And don't be afraid to add indexes if it looks like they might help.

11-20-2012, 10:26 PM	PM User \| #10
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,248 Thanks: 59 Thanked 3,998 Times in 3,967 Posts	I will say that if, for example, you wanted to search for (say) the word "people" in the TEXT of all 20,000 tweets, there is no good way to use an INDEX for that with MySQL. So, yes, MySQL would have to actually read all 20,000 records and look in the tweet text field to try to find that word. That is: Code: SELECT * FROM tweets WHERE tweet LIKE '%people%' will not be particularly fast. BUT... But say you put an index on the WHENTWEETED field and you knew that the tweet you are looking for was tweeted in the last two days: Code: SELECT * FROM tweets WHERE tweet LIKE '%people%' AND whenTweeted > DATE_SUB( NOW(), INTERVAL 2 DAY ) Now MySQL will only need to look at the full test of the tweet in records added in the last 2 days. The selection to find all records in the last 2 days will take perhaps 5 milliseconds and then the time to search the test will depend on how many records were posted in those 2 days. In other word, take advantage of your indexes! And don't be afraid to add indexes if it looks like they might help. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.