From: Devananda (karnah805yahoo.com)
Date: Fri Jul 01 2005 - 19:59:26 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

If you can, I recommend installing "mytop"
(http://jeremy.zawodny.com/mysql/mytop/) - it has helped me immensely to
identify which particular queries are putting the heaviest load on the
server.

>>I have a lot of two column tables consisting
>>of integer primary key and varchar in the second column.
>>I repeatedly search the second column

depending on how you search the second column, different types of
indexes will help you a LOT. searching on a column with a query like
"WHERE colname = 'value'", when there is no index on that column, always
results in a table scan, which is just what you don't want. However, if
your search is "WHERE colname LIKE '%some_string%'", then an ordinary
index won't help either - you will need a FULLTEXT index, and you will
also need to change the query to "WHERE MATCH colname AGAINST
'%some_string%'".

Good luck!
~Deva

Atle Veka wrote:
> Here are two ways to find the queries:
>
> 1) 'SHOW FULL PROCESSLIST\G' in the mysql client and taking note of what
> queries seem to be taking the most time
>
> 2) enable update logging and slow query logging
>
> When you have gathered a list of queries that you want to look into
> optimizing, run [in the client]: 'EXPLAIN <query...>' . That will give you
> an idea of where index(es) would benefit. Check the manual for EXPLAIN to
> decipher the output.
>
>
> Good luck!
>
> Atle
> -
> Flying Crocodile Inc, Unix Systems Administrator
>
> On Fri, 1 Jul 2005, Siegfried Heintze wrote:
>
>
>>Are there any tools for finding hot spots in one's database? My screen
>>scraper is maxing out my CPU. I'm thinking I might need some secondary
>>indexes in some of my tables. I have a lot of two column tables consisting
>>of integer primary key and varchar in the second column. I repeatedly search
>>the second column and, if there is no match, return mysql_insertid.
>>
>>
>>Are there any tools to help me tell which SQL statements are gobbling up my
>>CPU and disk? I suppose I could blindly put secondary indexes everywhere.
>>
>>Siegfried
>>
>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]