Re: Combining several queries from Wordpress Database

[Rick James]

JOIN wp_usermeta AS fn ON fn.id = u.id AND fn.meta_key="first_name" -- 3 new lines
JOIN wp_usermeta AS fn ON ln.id = u.id AND ln.meta_key="last_name"
JOIN wp_usermeta AS fn ON c.id = u.id AND c.meta_key="calibre"
-->
JOIN wp_usermeta AS fn ON fn.id = u.id AND fn.meta_key="first_name" -- 3 new lines
JOIN wp_usermeta AS ln ON ln.id = u.id AND ln.meta_key="last_name"
JOIN wp_usermeta AS c ON c.id = u.id AND c.meta_key="calibre"

My bad -- I failed to use different "aliases" for those two tables in this "self join".

Yeah, key-value schema. :(

The following change would help performance some:

CREATE TABLE `wp_usermeta` (
`umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`umeta_id`),
KEY `user_id` (`user_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=9280 DEFAULT CHARSET=utf8

-->

CREATE TABLE `wp_usermeta` (
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`user_id`, `meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8