I'm using PHP and MySQL/InnoDB.
I have a main News
table with around 500K rows.
Three other tables are Source
(max 500 rows), Category
(12 rows) and Tag
(average 2 tags for each record in News
) which I frequently use in join with News
. Most of my SELECT queries have three joins (between News
, Source
and Category
).
Another table is OldNews
which is used to store news that are not important for most of queries. They exist only for getting selected by id in permalinks. This table has about 3M records.
Sometimes strange queries get into the slow_query_log:
# Query_time: 30.839378 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
SET timestamp=1481914895;
# administrator command: Prepare;
(Edit: I don't know whether Prepare command belongs to this query or not. I just edited so it might help).
Another query:
# Query_time: 22.986259 Lock_time: 0.000026 Rows_sent: 0 Rows_examined: 0
SET timestamp=1481915053;
SELECT id FROM News WHERE id=1800004;
where id is the primary key, or
# Query_time: 18.006325 Lock_time: 0.000016 Rows_sent: 20 Rows_examined: 60
SET timestamp=1481915053;
SELECT STRAIGHT_JOIN
News.id, News.title,
Source.name AS source_name, Category.name AS category_name
FROM News
INNER JOIN Source ON News.source=Source.id
INNER JOIN Category ON News.category=Category.id
WHERE News.category=11
ORDER BY News.last_suggestion_time DESC limit 20;
where (category, last_suggestion_time) is a compound index in News
table.
So the question is:
What's making this mess? Is it that SET TIMESTAMP
?
Some server configs:
RAM: 4G
CPU: Dual Core 2000
MySQL: 5.5.31
Apache: 2.2.26
my.cnf
[mysqld]
innodb_buffer_pool_size = 2048M
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow_query_log.log
// these settings are suggested by mysqltuner, but there was no change in performance
innodb_buffer_pool_instances = 2
max_connections = 160
query_cache_type = 0
thread_cache_size = 4
innodb_file_per_table = 1