Professional Web Applications Themes

COUNT(*), datatype TEXT and temp disk tables - MySQL

Hi, I'm trying to tune this application where we're seeing something like 65% of temp tables wind up getting created on disk. We're using a lot of queries of the form: SELECT COUNT(*) FROM table1 WHERE ((field1 = 478 AND field2 = 2815) OR (field1 = 2815 AND field2 = 478)) Note that table1 has a TEXT column in it. Will the COUNT(*) cause the temp table to be created on disk? Should we instead do something like COUNT(id)? Thanks....

  1. #1

    Default COUNT(*), datatype TEXT and temp disk tables

    Hi, I'm trying to tune this application where we're seeing something
    like 65% of temp tables wind up getting created on disk.

    We're using a lot of queries of the form:

    SELECT COUNT(*) FROM table1
    WHERE ((field1 = 478 AND field2 = 2815)
    OR (field1 = 2815 AND field2 = 478))

    Note that table1 has a TEXT column in it.

    Will the COUNT(*) cause the temp table to be created on disk? Should we
    instead do something like COUNT(id)?

    Thanks.

    Guest

  2. #2

    Default Re: COUNT(*), datatype TEXT and temp disk tables


    org wrote: 

    Have you tried creating an index like:
    create index i1 on table1 (field1,field2);
     

    toby Guest

  3. #3

    Default Re: COUNT(*), datatype TEXT and temp disk tables

    In article <googlegroups.com>,
    toby <com.au> wrote: 
    >
    >Have you tried creating an index like:
    > create index i1 on table1 (field1,field2);
    >[/ref]

    Yes, those fields have indexes on them already.

    Guest

  4. #4

    Default Re: COUNT(*), datatype TEXT and temp disk tables

    In article <supernews.com>, <org> wrote: 

    Actually, I think the question I want to ask is whether there's a way to
    tell which queries are forcing MySQL to create temporary tables on disk,
    rather than handling them in memory. EXPLAIN doesn't seem to indicate
    either way. I forgot to mention that the server is 4.1.20.

    Thanks.

    Guest

  5. #5

    Default Re: COUNT(*), datatype TEXT and temp disk tables

    org wrote: 
    > >
    > >Have you tried creating an index like:
    > > create index i1 on table1 (field1,field2);
    > >[/ref]
    >
    > Yes, those fields have indexes on them already.[/ref]

    A multiple-column index as I indicated? Separate indices don't help
    that query much.

    Temporary tables are created on disk if they are larger than a
    threshold set by system variable tmp_table_size. But I would try
    creating a suitable index first of all.

    toby Guest

Similar Threads

  1. SP Temp Tables
    By navdeep virk in forum Informix
    Replies: 6
    Last Post: February 18th, 04:30 PM
  2. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  3. Schedule maitenance or temp tables
    By Shawn in forum ASP Database
    Replies: 2
    Last Post: August 21st, 03:12 PM
  4. Accessing Temp Tables
    By Karthik Nagaraj in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 12th, 11:26 PM
  5. Question about optimizer with (big) temp tables
    By Eric Mamet in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 08:44 AM

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