Query Optimization

[purus]

I'm try to design an efficient query to pull some data from a table and order it by the average of one of the columns. Here is what I have so far and it is exactly what I need:

 

SELECT items.title, items.realid, items.class, SUM( profit ) AS pft, AVG( profit ) / quantity AS avg, SUM( quantity ) AS qnt

FROM completed

INNER JOIN items ON completed.itemid = items.realid

WHERE userid = '1'

GROUP BY itemid

ORDER BY pft DESC

LIMIT 20

 

This takes about 20 seconds to run on a table of about 2000 rows. This is just a test table though and I expect the final version to be exponentially larger.

 

Is there any way to design a query that does the same thing but doesn't take such a huge amount of time to run? Something tells me I am going to have to look into caching of some sort, but I honestly don't know how to do such a thing so that this query would be available for every user individually (i.e. a slightly different version of the query for each user so that it only shows their data).  ???

[luca200]

Post the structure of your tables...

[purus]

Wow, I love quick responses 

 

--

-- Table structure for table `completed`

--

 

CREATE TABLE `completed` (

  `id` bigint(20) unsigned NOT NULL auto_increment,

  `userid` int(10) unsigned NOT NULL default '0',

  `itemid` int(10) unsigned NOT NULL default '0',

  `finalbid` int(10) unsigned NOT NULL default '0',

  `buyout` int(10) unsigned NOT NULL default '0',

  `date` int(10) unsigned NOT NULL default '0',

  `quantity` smallint(5) unsigned NOT NULL default '0',

  `fee` int(10) unsigned NOT NULL default '0',

  `priceper` double unsigned NOT NULL default '0',

  `profit` int(10) unsigned NOT NULL default '0',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1982 ;

 

--

-- Table structure for table `items`

--

 

CREATE TABLE `items` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `title` varchar(255) NOT NULL default '',

  `realid` mediumint( unsigned NOT NULL default '0',

  `class` enum('q0','q1','q2','q3','q4','q5','q6') NOT NULL default 'q0',

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22822 ;

[fenway]

Could we see the EXPLAIN too?

[purus]

Hope this displays ok. I'm using phpMyAdmin so the output is in tables...

 

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	completed 	ALL 	NULL 	NULL 	NULL 	NULL 	1971 	Using where; Using temporary; Using filesort
1 	SIMPLE 	items 	ALL 	NULL 	NULL 	NULL 	NULL 	22821 	Using where

[fenway]

I see lots of NULLs for key usage! No indexes...

 

Add an index to items.realid and completed.userid

[purus]

Holy crap

 

Query took 0.0569 sec

 

I'm going to go learn all about indexes now. Thanks!

[fenway]

No problem... keep watching that EXPLAIN.  There are a few stickies on this issue that I've posted.