MySql - WAMP - Huge Table is very slow (20 million rows)

Question

So I posted this! yesterday and got a perfect answer, which required running this code first: ALTER TABLE mytable AUTO_INCREMENT=10000001;

I ran it several times, but restarted WAMP after a couple of hours of it not working. After running overnight (12 hours), the code still hadn't run.

I am wondering if my database table size is past the limits of mysql or my computer or both.

However, I have a sneaky suspicion that proper indexing or some other factor could greatly impact my performance. I know 20 million is a lot of rows, but is it too much?

I don't know much about indexes, except that they are important. I attempted to add them to the name and state fields, which I believe I did successfully.

Incidentally, I am trying to add a unique ID field, which is what my post yesterday was all about.

So, the question is: Is 20 million rows outside the scope of MySql? If not, am I missing an index or some other setting that would help better work with this 20 million rows? Can I put indexes on all the columns and make it super fast?

As always, thanks in advance...

Here are the specs:

My PC is XP, running WAMPSERVER, Win32 NTFS, Intel Duo Core, T9300 @ 2.50GHz, 1.17 GHz, 1.98 GB or RAM

DB: 1 table, 20 million rows The size of the tables is: Data 4.4 Gigs, Indexes 1.3 Gigs, Total 5.8 Gigs

The indexes are set up on the 'BUSINESS NAME' and 'STATE' fields

The table fields are like this:

`BUSINESS NAME` TEXT NOT NULL, 
`ADDRESS` TEXT NOT NULL, 
`CITY` TEXT NOT NULL, 
`STATE` TEXT NOT NULL, 
`ZIP CODE` TEXT NOT NULL, 
`COUNTY` TEXT NOT NULL, 
`WEB ADDRESS` TEXT NOT NULL, 
`PHONE NUMBER` TEXT NOT NULL, 
`FAX NUMBER` TEXT NOT NULL, 
`CONTACT NAME` TEXT NOT NULL, 
`TITLE` TEXT NOT NULL, 
`GENDER` TEXT NOT NULL, 
`EMPLOYEE` TEXT NOT NULL, 
`SALES` TEXT NOT NULL, 
`MAJOR DIVISION DESCRIPTION` TEXT NOT NULL, 
`SIC 2 CODE DESCRIPTION` TEXT NOT NULL, 
`SIC 4 CODE` TEXT NOT NULL, 
`SIC 4 CODE DESCRIPTION` TEXT NOT NULL 

Answer 1

Some answers:

• 20 million rows is well within the capability of MySQL. I work on a database that has over 500 million rows in one of its tables. It can take hours to restructure a table, but ordinary queries aren't a problem as long as they're assisted by an index.

• Your laptop is pretty out of date and underpowered to use as a high-scale database server. It's going to take a long time to do a table restructure. The low amount of memory and typically slow laptop disk is probably constraining you. You're probably using default settings for MySQL too, which are designed to work on very old computers.

• I wouldn't recommend using TEXT data type for every column. There's no reason you need TEXT for most of those columns.

• Don't create an index on every column, especially if you insist on using TEXT data types. You can't even index a TEXT column unless you define a prefix index. In general, choose indexes to support specific queries.

You probably have many other questions based on the above, but there's too much to cover in a single StackOverflow post. You might want to take training or read a book if you're going to work with databases.
I recommend High Performance MySQL, 2nd Edition.

---

Re your followup questions:

For MySQL tuning, here's a good place to start: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

Many ALTER TABLE operations cause a table restructure, which means basically lock the table, make a copy of the whole table with the changes applied, and then rename the new and old tables and drop the old table. If the table is very large, this can take a long time.

A TEXT data type can store up to 64KB, which is overkill for a phone number or a state. I would use CHAR(10) for a typical US phone number. I would use CHAR(2) for a US state. In general, use the most compact and thrifty data type that supports the range of data you need in a given column.