Re: Re: Performance Problems With Two Tables With Over 500K Rows

[mos]

At 08:31 PM 11/26/2006, John Kopanas wrote:
>When I did a:
>
>SELECT * FROM purchased_services WHERE company_id = 1000;
>
>It took me 7 seconds.  This is driving me crazy!
>
>I am going to have to try this on another computer and see if I am
>going to get the same results on another system.   Argh...

Try running Optimize on the table to rebuild the index. Maybe there is 
something wrong with the index.
I assume you have to use InnoDb because you need transactions? If not, copy 
the data to a MyISAM table and retest it.

Here is some sample code to copy the table:

create table TableNew type=MYISAM select * from TableOld limit 0;
insert into TableNew select * from TableOld;
alter table TableNew add index ix_Index1 (Company_Id);
select * from TableNew where Company_Id=1000;

Mike