Re: How to speed up query of indexed column with 5M rows?

[Larry Kagan]

You've got 6 columns and 5 indexes. Generally speaking that's too much. Notice that your index file (MYI) is larger than the table itself (MYD). Cut down on the indexes. Also, you're using the full name column as an index, that's up to 255 chars! Cut that down and use a partial index.

KEY `flow_nameindex` (name(15)) -- Uses the first 15 chars of the column.

That should speed up your queries exponentially. If you want to take it a step further, make the name column a char(255). Yes, it will take up more space but will be much faster for I/O. This last change will only have a speed improvement for MyISAM tables.

Please respond back with the results of your changes.

Thanks,

Larry Kagan



Edited 2 time(s). Last edit at 10/09/2006 08:14AM by Larry Kagan.