|
|
Posted by james.gauth on 06/22/07 16:00
On 22 Jun, 16:28, Derrick Shields <derrick.shie...@gmail.com> 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
Hi,
If you are using MySQL, the following is a good reference to the
behaviour of indexes:
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
The query:
select * from people where last_name like '%smith%'
Would not be able to use an index, because there is no prefix to
search for (the search term can be found anywhere within the string).
The following queries would be able to use an index because the search
term forms a prefix:
select * from people where last_name like 'smith%'
select * from people where last_name like 'smith'
Bear in mind that adding indexes will slow down INSERT speed, the
following page provides approximate time values for INSERT statements
and the overheads that indexes create:
http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html
Regards,
James.
Navigation:
[Reply to this message]
|