Professional Web Applications Themes

removing temporary tables from queries - MySQL

The following query has to create a temporary table and do a filesort. I have many such queries and from what I can tell all occur due to a 'popularity' count. Any ideas on avoiding temporary table creations like this? SELECT tag.*, COUNT( tag.tag_id ) AS count FROM tag JOIN usertag ON usertag.tag_id = tag.tag_id WHERE tag.brand_id = '1' GROUP BY tag.tag_id ORDER BY tag.tag_id DESC LIMIT 0, 18 CREATE TABLE `tag` ( `tag_id` int(10) unsigned NOT NULL auto_increment, `name` varchar(64) NOT NULL default '', `brand_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`tag_id`), KEY `name` (`name`), KEY `brand_id` ...

  1. #1

    Default removing temporary tables from queries

    The following query has to create a temporary table and do a filesort.
    I have many such queries and from what I can tell all occur due to a
    'popularity' count. Any ideas on avoiding temporary table creations
    like this?

    SELECT tag.*, COUNT( tag.tag_id ) AS count
    FROM tag
    JOIN usertag ON usertag.tag_id = tag.tag_id
    WHERE tag.brand_id = '1'
    GROUP BY tag.tag_id
    ORDER BY tag.tag_id DESC LIMIT 0, 18

    CREATE TABLE `tag` (
    `tag_id` int(10) unsigned NOT NULL auto_increment,
    `name` varchar(64) NOT NULL default '',
    `brand_id` int(10) unsigned NOT NULL default '0',
    PRIMARY KEY (`tag_id`),
    KEY `name` (`name`),
    KEY `brand_id` (`brand_id`),
    FULLTEXT KEY `name_2` (`name`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1 ;

    CREATE TABLE `usertag` (
    `usertag_id` int(10) unsigned NOT NULL auto_increment,
    `user_id` int(10) unsigned NOT NULL,
    `tag_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`usertag_id`),
    KEY `user_id` (`user_id`),
    KEY `tag_id` (`tag_id`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    austinbarton@gmail.com Guest

  2. #2

    Default Re: removing temporary tables from queries

    com wrote: 

    Just where are you creating a temporary table here?


    Paul Guest

  3. #3

    Default Re: removing temporary tables from queries

    Run an EXPLAIN on it:

    1 SIMPLE tag ref PRIMARY,brand_id brand_id 4 const 1582
    Using where; Using temporary; Using filesort
    1 SIMPLE usertag ref tag_id tag_id 4 tag.tag_id 12 Using index


    Paul Lautman wrote: 
    >
    > Just where are you creating a temporary table here?[/ref]

    austinbarton@gmail.com Guest

Similar Threads

  1. Cannot work with temporary tables
    By Nafiganado in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 2nd, 03:25 PM
  2. MySql temporary tables
    By Andu in forum PHP Development
    Replies: 1
    Last Post: September 16th, 03:31 AM
  3. Handling temporary tables in Informix
    By Suket in forum Informix
    Replies: 4
    Last Post: September 5th, 07:17 AM
  4. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  5. global temporary tables
    By Blair Adamache in forum IBM DB2
    Replies: 0
    Last Post: July 31st, 07:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139