I am doing a job for a client with tables of between 100,000 records and 11 Million records - normally don't deal with anything that large. He is using MySQL 4.1.22-standard-log - on a Linux Server.
he receives leads posts from various sources automated-ly and can receive up to 5 a second from various clients.
the tables have no indexes on them except the auto-incrementing primary. they are MyISam tables
he has about 30-40 tables, each with the clients' names like adt, vis, _gci (I'm making these up), but the tables are all the exact same structure.
he said he tried having all tables be merged as one with a field "PartnerCode" which would contain adt, vis, gci etc., but that the tables would be locked and queries would fail.
that seems to not be necessary - is this a real limit or is there a way to have these tables all be merged into one and handle perhaps 100-200 queries per second? It seems mysql should be able to do this without a problem. Again, there are no indexes on the tables so no overhead on that regard
P.S. I really would like to add indexes so I can query the records, batch them and archive them to other locations etc.
