Slow query problem

Databases
1

Hey guys, hopefully you can help me with this one.

I have this query in my slow query log.

# Query_time: 10  Lock_time: 0  Rows_sent: 15  Rows_examined: 1644102
select P.id, M.username, P.name, T.name type, P.color, P.price, P.location, M.id userID from table1 P, table2 T, table3 M where (P.owner=M.id)and(P.pet_type=T.id) order by P.name ASC LIMIT 539235, 15;

Now obviously something is wrong…it takes 10 seconds and it has 1.6 million rows being examined. I can’t figure out where I’m going wrong though.

Here the the index’s I have:

[B]Table - P[/B]
Keyname: ownerptype
Field 1: owner
Field 2: pet_Type

Keyname: name
Field 1: name

[B]Table - M[/B]
Keyname: PRIMARY
Field 1: id

[B]Table - T[/B]
Keyname: PRIMARY
Field 1: id

From what I can tell I’m using all the correct index’s. When I run explain on the query it gives me the following information:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	P	index	ownerptype	name	252	NULL	900708	 
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

Any idea what I’m doing wrong here?

Thanks!

2

In the SELECT clause, which table is type coming from? How are the tables related?

Can you show a CREATE TABLE for the tables?

You should use the JOIN syntax:

SELECT
      p.id
    , m.username
    , p.name
    , t.name
    , type
    , p.color
    , p.price
    , p.location
    , m.userID
FROM
    M
INNER JOIN
    P
ON
    p.owner = M.id
INNER JOIN
    T
ON
    P.pet_type=T.id
ORDER BY
    P.name ASC
LIMIT
    539235, 15
3

Try adding an index on only pet_Type for table1, and maybe one on only owner as well. Right now you only have a combined key there, and it might not work well since you’re joining with only half of it. Just guessing, you’ll have to try.

4

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 ;

-- --------------------------------------------------------