please drop and recreate the indexes on pm_map.map_user_id and notifications.user_id, so that the indexes include the indicated additional columns as secondary columns (i.e. after user_id)
EXPLAIN (for the same query) now reveals:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY u ref username username 32 const 1 Using where
5 DEPENDENT SUBQUERY cashshop_stripe_txn ref user_id user_id 8 .u.id 2 Using index
4 DEPENDENT SUBQUERY cashshop_txn index user_id user_id 130 NULL 5494 Using where; Using index
3 DEPENDENT SUBQUERY notifications ref user_id user_id 12 .u.id,const 10 Using index
2 DEPENDENT SUBQUERY pm_map ref map_user_id map_user_id 10 .u.id,const,const 4 Using index
Is this more of where we want it to be in terms of optimization? I’ve noticed that these queries are still appearing in the slow query log. Is there more work to be done?
Is the intention to get the number of unread PMs that a user has?
Are the passwords stored as plain text or are they hashed?
Yes. It is intention to obtain the number of unread PMs. And no, passwords are clearly not stored in plaintext. I removed that information from the query.
With the new indexes, the CREATE TABLES are as follows:
CREATE TABLE `pm_map` (
`map_id` bigint(20) NOT NULL AUTO_INCREMENT,
`map_user_id` bigint(20) NOT NULL,
`map_topic_id` bigint(20) NOT NULL,
`map_active` tinyint(1) NOT NULL DEFAULT '1',
`map_unread` tinyint(1) NOT NULL DEFAULT '1',
`map_is_starter` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`map_id`),
KEY `map_topic_id` (`map_topic_id`),
KEY `map_user_id` (`map_user_id`,`map_active`,`map_unread`)
) ENGINE=InnoDB AUTO_INCREMENT=111868 DEFAULT CHARSET=latin1
CREATE TABLE `notifications` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`text` text NOT NULL,
`time` int(10) NOT NULL,
`icon` varchar(255) NOT NULL,
`growled` int(1) NOT NULL DEFAULT '0',
`metadata` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `time` (`time`),
KEY `metadata` (`metadata`),
KEY `user_id` (`user_id`,`growled`)
) ENGINE=InnoDB AUTO_INCREMENT=21312546 DEFAULT CHARSET=latin1
CREATE TABLE `cashshop_txn` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(127) DEFAULT NULL,
`payer_id` varchar(60) DEFAULT NULL,
`payment_date` varchar(50) DEFAULT NULL,
`txn_id` varchar(50) DEFAULT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`payer_email` varchar(75) DEFAULT NULL,
`payer_status` varchar(50) DEFAULT NULL,
`payment_type` varchar(50) DEFAULT NULL,
`memo` tinytext,
`item_name` varchar(127) DEFAULT NULL,
`quantity` int(11) NOT NULL DEFAULT '0',
`mc_gross` decimal(9,2) DEFAULT NULL,
`mc_currency` char(3) DEFAULT NULL,
`address_name` varchar(255) NOT NULL DEFAULT '',
`address_street` varchar(255) NOT NULL DEFAULT '',
`address_city` varchar(255) NOT NULL DEFAULT '',
`address_state` varchar(255) NOT NULL DEFAULT '',
`address_zip` varchar(255) NOT NULL DEFAULT '',
`address_country` varchar(255) NOT NULL DEFAULT '',
`address_status` varchar(255) NOT NULL DEFAULT '',
`payer_business_name` varchar(255) NOT NULL DEFAULT '',
`payment_status` varchar(255) NOT NULL DEFAULT '',
`pending_reason` varchar(255) NOT NULL DEFAULT '',
`reason_code` varchar(255) NOT NULL DEFAULT '',
`txn_type` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `txn_id` (`txn_id`),
KEY `txn_id_2` (`txn_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5462 DEFAULT CHARSET=latin1
CREATE TABLE `cashshop_stripe_txn` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`amount` int(11) NOT NULL,
`stripe_transaction_id` varchar(255) NOT NULL DEFAULT '',
`time` int(10) NOT NULL,
`address_street` varchar(255) DEFAULT '',
`address_street2` varchar(255) DEFAULT '',
`address_city` varchar(255) DEFAULT '',
`address_state` varchar(255) DEFAULT '',
`address_country` varchar(255) DEFAULT '',
`address_zip` int(10) DEFAULT NULL,
`address_name` varchar(255) DEFAULT '',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=926 DEFAULT CHARSET=latin1
CREATE TABLE `users` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`session_active` int(1) NOT NULL DEFAULT '0',
`session_lastclick` int(10) NOT NULL DEFAULT '1',
`session_consecutive_days` int(10) NOT NULL DEFAULT '0',
`activated` int(1) NOT NULL DEFAULT '0',
`email` varchar(100) NOT NULL,
`registered` int(10) NOT NULL,
`referral_location` varchar(255) DEFAULT '',
`last_login` int(10) NOT NULL,
`ip_registered` varchar(30) NOT NULL,
`ip_last_login` varchar(30) NOT NULL,
`gender` varchar(6) NOT NULL,
[...]
PRIMARY KEY (`id`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=80873 DEFAULT CHARSET=latin1
Thanks in advanced. I appreciate it.