You might be out of luck with MySQL ... sorry.
You may need to switch to a database that has a parallel query facility. Then - every query becomes a massive table scan but gets divided into multiple concurrent subqueries - and overall the job finishes in a reasonable amount of time. The epitomy of brute force. It's hard to rationalize initially but after a while you see it's the only way to go. Remember - indexes are no longer required.
We have a billion row 100GB table the users search any and every way. Response time is less than a minute.
We are anxiously waiting to see this technology added to MySQL. Maybe one day we'll have some money to contribute to the effort. Parallel query is not trivial. That's why these databases are expensive.
I can send you more details privately if you are interested.
I've used it, with oracle, but oracles index searches are better, hit the best one first, then 2nd best, then 3rd, but I really dont want to go to oracle, it's too complicated for my tech's
vs mysql, hit the best one first, and use no other
Query time is a non issue at this point, it's load time, load daily file into temp table, then insert ignore into main table, on key violation the violating record is ignored
load time is the issue, the server loads files 15 hours a day, that big primary key makes loading any table over 2-3 gig VERY slow
I thought it was a bug, everything was great untill you get up to about 3 or 4 gig, then it gets SLOW
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
