List:General Discussion« Previous MessageNext Message »
From:mos Date:November 27 2006 4:35am
Subject:Re: Re: Performance Problems With Two Tables With Over 500K
Rows
View as plain text  
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
Thread
Performance Problems With Two Tables With Over 500K RowsJohn Kopanas25 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas26 Nov
    • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas26 Nov
      • Re: Re: Re: Performance Problems With Two Tables With Over 500K RowsDan Buettner26 Nov
        • Re: Re: Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson26 Nov
    • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
      • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson27 Nov
        • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
          • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson27 Nov
            • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
          • Re: Re: Performance Problems With Two Tables With Over 500KRowsmos27 Nov
        • Re: Performance Problems With Two Tables With Over 500K RowsDominik Klein27 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsDuncan Hill27 Nov