I wish we had thought to have had him benchmark a query before and after he added an index. It would be interesting to see the difference in actual time that an index can make on a table with 450,000 records.
On Wed, 2004-10-06 at 15:31, Jason Williard wrote: > Amit, > > You are awesome! That fixed it quite nicely. Our system is screaming > now :-) > > Thank You VERY MUCH!, > Jason Williard > Client Services > > > > [EMAIL PROTECTED] wrote: > > Do this and tell me if it helped > > > > ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) > > > > Regards, > > Amit > > > > -----Original Message----- > > From: Jason Williard [mailto:[EMAIL PROTECTED] > > Sent: Thursday, October 07, 2004 1:42 AM > > To: Wadhwa, Amit > > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Subject: Re: Long Running Queries > > > > Thank you for your assistance with this. Here's what you were asking > > for. > > > > Structure Output: > > # > > # Table structure for table 'asticketsdata' > > # > > > > CREATE TABLE asticketsdata ( > > ticketdataid int(11) NOT NULL auto_increment, > > ticketidchar varchar(255) NOT NULL default '', > > ticketidno int(11) default NULL, > > contents text, > > emailbody text, > > PRIMARY KEY (ticketdataid), > > KEY ticketdata1 (ticketidno) > > ) TYPE=MyISAM; > > > > Query: > > SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393'; > > > > Thank You, > > Jason Williard > > Client Services > > > > > > > > [EMAIL PROTECTED] wrote: > > > >>I have tables with millions of records. > >> > >>1. I use Query-Caching and that speeds up all my selects/searches > >>after the first time, no matter how complicated the criteria. > >>2. Build Indexes on the columns that are in your 'where' Criteria, > >>that will help you big time. > >> > >>If you post the table structure, and the query, I can help you with > >>your index. > >> > >>-----Original Message----- > >>From: Jason Williard [mailto:[EMAIL PROTECTED] > >>Sent: Thursday, October 07, 2004 1:23 AM > >>To: Wadhwa, Amit > >>Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > >>Subject: Re: Long Running Queries > >> > >>I'm sorry to sound like a novice, but could you explain that? Are you > > > > > >>recommending that I set the column I am searching to an Index? > >> > >>Thank You, > >>Jason Williard > >>Client Services > >> > >> > >> > >>[EMAIL PROTECTED] wrote: > >> > >> > >>>You need Appropriate Indexes on the tables. > >>> > >>>-----Original Message----- > >>>From: Jason Williard [mailto:[EMAIL PROTECTED] > >>>Sent: Thursday, October 07, 2004 1:11 AM > >>>To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > >>>Subject: Long Running Queries > >>> > >>>Hello, > >>> > >>>I recently began experiencing issues that I would like some assistance > >> > >> > >>>with. > >>> > >>>Server Details: > >>> - Windows 2003 > >>> - MySQL Max 4.0.20a > >>> > >>>I have a table with 450,000+ records in it. When I try to run a > >>>single query, such as selecting 1 row or deleting 1 row using the > >>>WHERE clause, the query can take forever to run. In a specific case, > >>>it took 57 seconds to complete the SELECT query. > >>> > >>>Any ideas on how to speed this up? > >> > >> > >> > >> > >> > > > > > > > > >
