472,102 Members | 2,191 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,102 software developers and data experts.

Slow Queries, 4 million records, need educated advice!

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.

Dual Xeon 3.06 Ghz
800GB RAID5 SATA array

FreeBSD 5.3
Apache 2.0
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

The action performed is

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


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.

Jun 29 '06 #1
1 2636
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

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:

Bill K.
Jun 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by DJJ | last post: by
4 posts views Thread by psql-mail | last post: by
reply views Thread by Dave Hammond | last post: by
12 posts views Thread by grace | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.