FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Optimizing a mySQL database





GSIS
I've been getting a lot of blank pages served, recently. I've also been getting timeouts and disconnects. While doing some digging I took a look at the stats for my live mySQL database and found that "Handler_read_rnd_next" is at 214M.

This suggests that indexes are missing, for one or more queries, causing mySQL to try to read a whole table instead of finding the data via indexes.

Is there a way of discovering which queries are doing this, or do I have to go through the entire system by hand to locate and analyse each one?
kv
use EXPLAIN with your queries. It will give you a overview of how your queries are treated. check for possible_keys column for indexes used and Extra column to see if any file sort or temporary tables are used.
Bondings
Go to the source of the page that takes so much time to load and echo the time at several intervals in the source. Then narrow it down and take a look at what pieces of code (usually mysql) take the most time. Run those queries with an EXPLAIN thing in phpMyAdmin and take a look at the result. Setup an index for the columns that are used there and don't have one yet.
GSIS
Bondings wrote:
Go to the source of the page that takes so much time to load and echo the time at several intervals in the source. Then narrow it down and take a look at what pieces of code (usually mysql) take the most time. Run those queries with an EXPLAIN thing in phpMyAdmin and take a look at the result. Setup an index for the columns that are used there and don't have one yet.


Why didn't I think of that? Laughing

I was hoping there was a way of temporarily switching on a debugging tool in mySQL to do the same sort of thing. Sort of an EXPLAIN all queries as they run.

Oh well. Here goes for a mammoth investigation into SQL and indexes. All part of the fun...

I suppose another thing I should be doing is setting up a benchmarking copy of the system (on my own hardware) so I can figure out if what I change really makes any difference.

Many thanks for the hint.
infobankr
There is an option in the mysql config to log slow queries, have you tried that?
Related topics
Cron Job to Backup MySQL Database
Mysql database problem
PHPBB MYSQL Database Question.
MySQL database for a forum
Mysql database size?
Connect to MySql database
[MySQL database] Password and username
insert data to mySQL database problem
MySQL Database!?
Delete data from MySQL database problem!!
MySQL database
Problem connecting to MySQL database with PHP [RESOLVED]
Need Help with mysql database of DragonflyCMS
Help importing CSV file into MySQL database
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.