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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
