Re: MySQL 5.5.31 cripplingly slow
Wow, thanks so much! What an educational experience this has been. I modified my key_buffer_size according to the results of that (the innodb_buffer_pool_size was good as I had it). I apologize if this is easily answered through a google search, but why don't you want the key_buffer_size to be bigger than a certain value? For instance, 20% of my RAM is larger than the value I obtained through your new method: what's the downside of setting key_buffer_size too high?
There's only been one slow query logged. It should only be executed once per iteration of the long script, and it still doesn't take too long so I'm not particularly worried about it, but if you care to take a look, here's the outputs you requested:
# Query_time: 1.157580 Lock_time: 0.000151 Rows_sent: 1447 Rows_examined: 5788
SELECT
craigusers.id, craigusers.email, craigusers.url, maxprice, craigusers.location, `date`, description, craigusers.userid, pid, empty,
cusers.email AS userEmail,
preferences.from
FROM
craigusers
JOIN
cusers
ON
craigusers.userid = cusers.id
JOIN
preferences
ON
craigusers.userid = preferences.userid
WHERE
`delete`=0
ORDER BY
craigusers.id;
------------------------------------------------------------------------
SHOW CREATE TABLE `craigusers`;
CREATE TABLE `craigusers` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`email` varchar(50) CHARACTER SET latin1 NOT NULL,
`url` varchar(250) CHARACTER SET latin1 NOT NULL,
`maxprice` varchar(7) CHARACTER SET latin1 NOT NULL DEFAULT 'none',
`location` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT 'any',
`date` int(10) NOT NULL,
`description` varchar(250) CHARACTER SET latin1 NOT NULL,
`pid` int(10) unsigned DEFAULT NULL,
`userid` int(11) NOT NULL DEFAULT '37',
`delete` tinyint(3) unsigned NOT NULL,
`replyto` varchar(320) DEFAULT NULL,
`added` datetime NOT NULL,
`updated` bigint(20) unsigned DEFAULT NULL,
`empty` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `userid` (`userid`),
KEY `delete` (`delete`),
KEY `updated` (`updated`),
KEY `pid` (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=15135 DEFAULT CHARSET=utf8
SHOW CREATE TABLE `cusers`;
CREATE TABLE `cusers` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL,
`salt` varchar(512) NOT NULL,
`password` varchar(512) NOT NULL,
`email` varchar(512) NOT NULL,
`access` tinyint(2) unsigned NOT NULL,
`created` datetime NOT NULL,
`code` char(36) DEFAULT NULL,
`pro` tinyint(3) unsigned NOT NULL DEFAULT '0',
`deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `pro` (`pro`),
KEY `deleted` (`deleted`)
) ENGINE=MyISAM AUTO_INCREMENT=3102 DEFAULT CHARSET=utf8
SHOW CREATE TABLE `preferences`;
CREATE TABLE `preferences` (
`userid` int(11) unsigned NOT NULL COMMENT 'id from cusers',
`url` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display whole url',
`email` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display watching email',
`price` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display max price',
`location` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'display location',
`placeid` int(11) unsigned NOT NULL COMMENT 'base url from places',
`from` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'mail from posting',
`sortColumn` varchar(8) NOT NULL,
`sortOrder` varchar(4) NOT NULL DEFAULT 'asc',
`enginealerts` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Alert user about engine status',
PRIMARY KEY (`userid`),
KEY `enginealerts` (`enginealerts`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='User preferences for craigwatch'
------------------------------------------------------------------------
SHOW TABLE STATUS LIKE 'craigusers';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| craigusers | MyISAM | 10 | Dynamic | 2874 | 202 | 580968 | 281474976710655 | 157696 | 332 | 15135 | 2014-01-10 23:50:09 | 2014-01-18 09:44:01 | 2014-01-14 22:30:12 | utf8_general_ci | NULL | | |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.70 sec)
SHOW TABLE STATUS LIKE 'cusers';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| cusers | MyISAM | 10 | Dynamic | 3007 | 237 | 715300 | 281474976710655 | 77824 | 0 | 3102 | 2013-11-10 06:37:25 | 2014-01-17 19:52:33 | 2014-01-14 22:30:12 | utf8_general_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
SHOW TABLE STATUS LIKE 'preferences';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------+
| preferences | MyISAM | 10 | Dynamic | 3042 | 20 | 60924 | 281474976710655 | 56320 | 0 | NULL | 2013-10-17 06:48:54 | 2014-01-18 08:48:16 | 2014-01-14 22:30:13 | utf8_general_ci | NULL | | User preferences for craigwatch |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------------------------+
1 row in set (0.00 sec)
------------------------------------------------------------------------
EXPLAIN SELECT craigusers.id, craigusers.email, craigusers.url, maxprice, craigusers.location, `date`, description, craigusers.userid, pid, empty, cusers.email AS userEmail, preferences.from FROM craigusers JOIN cusers ON craigusers.userid = cusers.id JOIN preferences ON craigusers.userid = preferences.userid WHERE `delete`=0 ORDER BY craigusers.id;
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+-----------------------------+
| 1 | SIMPLE | craigusers | ref | userid,delete | delete | 1 | const | 1352 | Using where; Using filesort |
| 1 | SIMPLE | cusers | eq_ref | PRIMARY | PRIMARY | 4 | dsr.craigusers.userid | 1 | Using where |
| 1 | SIMPLE | preferences | eq_ref | PRIMARY | PRIMARY | 4 | dsr.craigusers.userid | 1 | Using where |
+----+-------------+-------------+--------+---------------+---------+---------+-----------------------+------+-----------------------------+
3 rows in set (0.03 sec)
The only comment I'd like to make about the above output is that I'm quite embarrassed by my tables. I created them 11 years ago and I would do things a bit differently now, including using better names, particularly ones that don't use reserved words and ones that are more descriptive.
Thanks again for everything!