Making Stuff Faster -- MySQL to be Specific · Sep 15, 03:47 PM by Dylan Doxey
I don't have much patience for slow running programs. Heck, I could type the data into the database faster than this one particular program.
Sounds like an opportunity to employ the slow query log feature. It's easy enough to enable. Just open up /etc/mysql/my.cfn and uncomment the relevant lines.
# Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2
Once you've accomplished that, just tail the query log while you program runs and become enlightened.
$: tail -F /var/log/mysql/mysql-slow.log # Time: 110915 14:35:41 # User@Host: url_urs[url_usr] @ localhost [] # Query_time: 375.707403 Lock_time: 0.000260 Rows_sent: 1 Rows_examined: 1200487 SET timestamp=1316122541; SELECT url, url_id FROM url WHERE visit_date = 0 ORDER BY RAND() LIMIT 1;
Well, there's the culprit. If the query only wants one result, then obviously there's no need to examine 1.2 million records to find it.
Fortunately, there's a convenient way to inform the dataserver about this obviousness.
$: mysql url_db -p -u url_usr mysql> CREATE INDEX visit_date_index ON url(visit_date); Query OK, 614526 rows affected (5 min 31.02 sec) Records: 614526 Duplicates: 0 Warnings: 0
That should do it.
Commenting is closed for this article.