Posted by Derrick Shields on 06/22/07 18:31

On Jun 22, 11:04 am, Joe Scylla <joe.scy...@gmail.com> wrote:
> Derrick Shields wrote:
> > I'm working with a database that has over 11 million rows. The .SQL
> > drop file is about 2.5gigs. Doing a simple query:
>
> > select * from people where last_name like '%smith%'
>
> > A query like this can take up to two minutes. I've spent hours reading
> > these forums and other sites on the internet, and I've figured that I
> > need to do a couple things:
>
> > create some indexes -and- possibly create some partitions
>
> > One thing I couldn't figure out though is what order to do these
> > things. Is creating partitions even necessary? Also, I tried creating
> > indexes on first_name and last_name columns, and it seems like it's
> > just freezing. It sat there for hours and never responded. Is the
> > database getting too big at this point to create indexes?
>
> > Thanks for any help anyone can offer!
>
> > Derrick
>
> First one:
> Try to avoid Statement as "LIKE = '%value%'" with percentage sign on
> every side of the search string because for such queries indexes can get
> used.
>
> Use if possible:
> "LIKE = 'value%'" (only percentage sign on the right side)
>
> And yes - create indexes. That helps alot in performance on tables with
> many rows. Creating the indexes can take alot of time - this all depends
> on your machine. If the mysql process still eats cpu time it should be ok.

So here is my question. What is the difference between LIKE 'value'
and LIKE '%VALUE%'? When you add the percentage signs, I understand
the percentage signs are wild cards, but if you're using LIKE without
wildcard percentage signs, is that technically the same as saying
"="?

Also, regarding adding some indexes to my table, it could potentially
take many hours or even days to perform that function? The size of
the database is about 2.5 Gigs with 11 Million Rows.

[Back to original message]