Ok, so the query I had in the form of INNER JOIN’s would look like this, SpacePhoenix. Type and userID were just renamed fields.
SELECT
P.id
, M.username
, P.name
, T.name as `type`
, P.color
, P.price
, P.location
, M.id as `userID`
FROM
table1 M
INNER JOIN
table2 P
ON
P.owner = M.id
INNER JOIN
table3 T
ON
P.pet_type=T.id
ORDER BY
P.name ASC
LIMIT
539235, 15
In this format, the query still takes 10 seconds and returns this EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE P index ownerptype name 252 NULL 900536
1 SIMPLE M eq_ref PRIMARY PRIMARY 4 gubbax_new2.P.owner 1
1 SIMPLE T eq_ref PRIMARY PRIMARY 4 gubbax_new2.P.pet_type 1
Here are my create table’s…
--
-- Table structure for table `table1`
--
CREATE TABLE IF NOT EXISTS `table1` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
`first_name` varchar(50) NOT NULL default '',
`last_name` varchar(50) NOT NULL default '',
`dob` varchar(50) NOT NULL default '',
`gender` varchar(50) NOT NULL default '',
`birthday` int(11) NOT NULL,
`nextbirth` int(11) NOT NULL,
`age` int(11) NOT NULL default '0',
`signup_date` varchar(50) NOT NULL default '',
`signup_ip` varchar(50) NOT NULL default '',
`last_login` varchar(50) NOT NULL default '',
`activated` int(11) NOT NULL default '0',
`username` varchar(50) NOT NULL default '',
`money` int(11) NOT NULL default '0',
`location` varchar(50) NOT NULL default '',
`credits` int(11) NOT NULL default '0',
`user_level` int(11) NOT NULL default '0',
`profile` text NOT NULL,
`sig` text NOT NULL,
`avatar` int(11) NOT NULL default '1',
`pid` int(11) NOT NULL default '0',
`prank` int(11) NOT NULL default '0',
`banned` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `username` (`username`(1)),
KEY `money` (`money`),
KEY `viewNews` (`viewNews`),
KEY `lastseentime` (`lastseentime`),
KEY `email` (`email`),
KEY `password` (`password`),
KEY `partyID` (`partyID`),
KEY `partyRank` (`partyRank`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=119563 ;
-- --------------------------------------------------------
--
-- Table structure for table `table2`
--
CREATE TABLE IF NOT EXISTS `table2` (
`id` int(11) NOT NULL auto_increment,
`owner` int(11) NOT NULL default '0',
`name` varchar(250) NOT NULL default '',
`pet_type` int(50) NOT NULL default '0',
`image_path` varchar(250) NOT NULL default '',
`gender` varchar(250) NOT NULL default '',
`age` int(11) NOT NULL default '0',
`level` int(11) NOT NULL default '0',
`health` int(11) NOT NULL default '0',
`current_health` int(11) NOT NULL default '0',
`hunger` int(11) NOT NULL default '0',
`power` int(11) NOT NULL default '0',
`defense` int(11) NOT NULL default '0',
`agility` int(11) NOT NULL default '0',
`intelect` int(11) NOT NULL default '0',
`element` int(11) NOT NULL default '0',
`rarity` int(11) NOT NULL default '0',
`emotion` int(11) NOT NULL default '0',
`characteristics` text NOT NULL,
`selected` int(11) NOT NULL default '0',
`color` varchar(50) NOT NULL default '',
`location` int(5) NOT NULL default '0'
PRIMARY KEY (`id`),
KEY `hunger` (`hunger`),
KEY `intellect` (`intelect`),
KEY `power` (`power`),
KEY `defense` (`defense`),
KEY `agility` (`agility`),
KEY `location` (`location`),
KEY `ownerptype` (`owner`,`pet_type`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1720774 ;
--
-- Table structure for table `table3`
--
CREATE TABLE IF NOT EXISTS `table3` (
`id` int(5) NOT NULL auto_increment,
`name` varchar(25) NOT NULL default '',
`image_path` varchar(50) NOT NULL default '',
`health` int(5) NOT NULL default '0',
`power` int(5) NOT NULL default '0',
`defense` int(5) NOT NULL default '0',
`agility` int(5) NOT NULL default '0',
`intelect` int(5) NOT NULL default '0',
`element` int(5) NOT NULL default '0',
`rarity` int(5) NOT NULL default '0',
`item_id` int(5) NOT NULL default '0',
`incubation` int(5) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `element` (`element`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;
-- --------------------------------------------------------