I have a bunch of user files in the hundreds of thousands and will probably explode into the tens of millions (maybe a few orders of magnitude more) that I am storing in a mySQL DB.
In our case: one of the offending tables has less than 20,000 rows but is about 2Gigs in size. There is a mediumblob column in this table that causes even a simple SELECT to take as much as a minute - I'm talking about a SELECT on PRIMARY KEY taking over a minute such as SELECT * FROM OFFENDING_TABLE where id='735'
We think we have indexing right... regardless why would such a simple query take so long?
We also did another simple test where we copied over the table (exported it) as another table, dropped the mediumblob column and ran the same queries. The queries now took a fraction of a second to run - including complex queries with large multiple joins.
So what could I be doing wrong? What can we do differently? Would appreciate any feedback, anyone can offer. --