MySQL Query Optimization for User Session

Hi,

We have the following MySQL query, which is executed on each page of the web site to get a user’s session data:


EXPLAIN SELECT	u.*, (	SELECT COUNT(*) FROM pm_map
						WHERE map_user_id = u.id
							AND map_active = 1
							AND map_unread = 1
					) AS unread_mail,
					(	SELECT COUNT(*) as count
							FROM notifications
							WHERE user_id = u.id
								AND growled = 0
						) AS unread_notifications,
					( SELECT COUNT(*) as count FROM cashshop_txn WHERE user_id = u.id ) AS cashshop_paypal,
					( SELECT COUNT(*) as count FROM cashshop_stripe_txn WHERE user_id = u.id ) AS cashshop_stripe
		FROM users u
		WHERE u.username = 'TEST' AND u.password = 'ASDF'

An EXPLAIN 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	8	---.u.id	17	Using where
2	DEPENDENT SUBQUERY	pm_map	ref	map_user_id	map_user_id	8	---.u.id	6	Using where

We recently added in the log-queries-not-using-indexes option into my.cnf and this query has been the culprit. How might we be able to go about optimizing this query?

Variables:

[list][]unread_mail - number of unread messages a user has
[
]unread_notifications - number of unread notifications a user has
[]cashshop_txn - whether or not a user has processed a cash shop transaction via Paypal
[
]cashshop_stripe_txn - whether or not a user has processed a cash shop transaction via Stripe[/list]
(We see if they have a cash shop transaction to determine if they are a “free” user or “paid” user on the site. Application logic handles the COUNT(*) returned.)

Indexes exist on:

[list][]pm_map.map_user_id
[
]notifications.user_id
[]cashshop_txn.user_id
[
]cashshop_stripe_txn.user_id
[*]u.id[/list]
Thanks in advanced.

Can you please post the output of SHOW CREATE TABLE queries for each table involved in the query?

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?

if you look at the EXPLAIN, and compare lines 5, 4, 3, and 2, you will notice that two of them are using an index, the other two aren’t

allow me to re-post your query, highlighting what i think might be the problem areas –


SELECT u.*
     , ( SELECT COUNT(*) 
           FROM pm_map
          WHERE map_user_id = u.id
           [COLOR="#FF0000"]AND map_active = 1
           AND map_unread = 1[/COLOR] ) AS unread_mail
     , ( SELECT COUNT(*) as count
           FROM notifications
          WHERE user_id = u.id
            [COLOR="#FF0000"]AND growled = 0[/COLOR] ) AS unread_notifications
     , ( SELECT COUNT(*) as count 
           FROM cashshop_txn 
          WHERE user_id = u.id ) AS cashshop_paypal
     , ( SELECT COUNT(*) as count 
           FROM cashshop_stripe_txn 
          WHERE user_id = u.id ) AS cashshop_stripe
  FROM users u
 WHERE u.username = 'TEST' 
   AND u.password = 'ASDF'

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)

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.

everything looks okay to me in the EXPLAINs

so i guess i’ve reached my DBA-fu limit here (i’m not really a DBA)

by the way, the KEY here is redundant, both of these create the same index –


  UNIQUE KEY `txn_id` (`txn_id`),
  KEY `txn_id_2` (`txn_id`),

Great. Thanks for pointing out that duplicate key. We’ll roll with this and see how it effects performance.