When will this MySQL table become slow

1
votes

Table shown below is:

  1. searched only [simple select], no deleting/inserting/joining with others etc.
  2. columns are set / no changes here
  3. 99%+ of the time searches will go by same column, which is indexed (column: "key" in example)
  4. engine: MyISAM
  5. row format: dynamic
  6. there are 2 other indexes (columns: id/location)

Since this table will be used with every page load, I'm concerned with size vs. speed ratio. Up to how many rows (roughly) will it be very fast, then fast, then slow, then reduced to crawl?

[columns name]   |  [data type]       |   [collation]
id               |   int(11)            
name             |   varchar(64)      |   utf8_general_ci       
key              |   varchar(64)      |   utf8_general_ci    |   [ 99% used for search:  is indexed]    
value            |   text             |   utf8_general_ci       
identifier_id    |   int(11)            
sort_order       |   int(5)             
last_adjusted    |   datetime           
location         |   varchar(255)     |   utf8_general_ci       
group_no         |   int(3)
1
mysqlperformance
post the results of show indexes from <your table> also a typical query with explain plan. - Jon Black
Tables aren't "slow". Queries might be. - a_horse_with_no_name
@a_horse_with_no_name that's what I meant - access to data in table depending on table size - a shortcut, but you have a point - sounds kinda silly - Jeffz

1 Answers

1
votes

That very much depends on your server setup.

The amount of memory available to the MySQL server being the main concern, then the type of storage the table is configured for.

You do not specify what type of indexes are in use, is key a UNIQUE index, or something with duplicates? I would guess at UNIQUE, given the usage - this would remain fast for a very considerable time period. If you keep in mind that the efficiency is approximately o(log n) for searching a unique index, and that the search itself on such an index is relatively trivial, then the index would have to be out of main memory and on some pretty slow media to make much of a difference.