Slow Queries, 4 million records, need educated advice!

[achurin]

Hi everybody, I am a rookie at the forum stuff. Please don't bash me
too badly.

I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

There has to be better solution! All you geniuses out there, UNITE!

Thanks in advance, Alex.

[Bill Karwin]

ac*****@gmail.com wrote:

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

Using a pattern like that cannot use indexes to do the search. So it
has to physically scan all 4 million rows to find those that match the
condition.

It's hard to give specific advice without seeing your schema or
understanding the nature of searches you need to do. Have you
considered using FULLTEXT indexes? Read about them here:
http://dev.mysql.com/doc/refman/5.0/...xt-search.html

Regards,
Bill K.