Re: Working with large datasets

Jason Pruim pruimj@gmail.com On Oct 10, 2011, at 2:20 PM, Jim Giner wrote: > > ""Toby Hart Dyke"" <toby@hartdyke.com> wrote in message > news:00da01cc8768$ca9e9200$5fdbb600$@hartdyke.com... >> >> It sounds as though you don't have an index on the right field. 8 million >> records should be no problem if you have the right indexes applied, and >> you're not trying to do anything too complicated. >> >> Toby > > I doubt that the State field is a primary index, or that it would be used as > one, which means that it could be a secondary one. If it is - that would be > a pretty long record itself and could be the problem therein. With > virtually all the records tied to one secondary key it is mostly a worthless > secondary index. I'd try removing it and seeing what happens. Actually it will be in the end if they keep going with the site... It's a "Report who called you and why" type of site. So for SEO purposes I'm changing the links from: index.php?phone=XXXXXXXXX&state=NY to: /new-york/XXXXXXXXXX RIght now though I only have 1 state inputed to work with though. I may need to just increase the max execution time as well... But it still runs too slowly ... Even from the commandline searching for a simple: SELECT * from Table WHERE state="test"; takes 56.96 seconds to search and returns only 2 records with 4 columns... Could this just be a hardware problem? Here is the structure of the table Im working with: +--------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+----------------+ | ID | int(11)| NO | PRI | NULL | auto_increment | | phone| text | NO | MUL | NULL | | | config | text | NO | | NULL | | | state | text | YES | | NULL | | +--------+---------+------+-----+---------+----------------+ I'm starting to lean more towards it being a problem with hardware though... I'm going to try and get the specs of the machine it's running on... (Not my host :))