Re: Long Running Queries

[Jason Williard]

Before: 57 seconds
After: <1 second  :-D
Thank You,
Jason Williard
Client Services

Spenser wrote:
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?





/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]