|
Posted by Joe Scylla on 06/22/07 16:04
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.
Navigation:
[Reply to this message]
|