Howto Store 6-7 Million Entries in MySQL?

[HoboTraveler]

Hello,

I loaded up a table (MyISAM) with 6 million rows. The performance is really slow. Doing a search on a varchar field takes about 300 secs.

I'd like to know how to go about optimizing the DB? There are about 26 columns. 20 columns are varchar and 6 are int type.

Is the performance decent, when loading 6 millions rows into a table?

TIA

[LifeinAsia]

Step 1- put indexes on your table.
Step 2- when possible, change to searching by int instead of varchar. Varchar searches are always slower than by int.

For step 1, put indexes on the columns you most frequently search on. Don't go too crazy though.

For step 2, determine if the varchar columns really need to be varchars. If the values are all of a certain set (e.g., red, blue, green), make a new table and put that set into it, using an integer key to reference with the original table.

In other words, instead of having a "Color" varchar field in the main table, change it to "ColorID" with a type of int. Make a new table called "ColorIDs" with "ColorID" type int and "Color" type varchar. Now if you have to go a varchar search on color, you do the search on the much smaller "ColorIDs" table, then find all the entries in the main table with the matching ColorID.