COUNT(*), datatype TEXT and temp disk tables

#1

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.

#2

postmaster (AT) cjc (DOT) org wrote:

Quote:

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)?

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

Quote:

Thanks.

#3

In article <1158899368.955665.296060 (AT) e3g2000cwe (DOT) googlegroups.com>,
toby <toby (AT) telegraphics (DOT) com.au> wrote:

Quote:

postmaster (AT) cjc (DOT) org wrote:
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)?

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

Yes, those fields have indexes on them already.

#4

In article <12h7ae64kun382d (AT) news (DOT) supernews.com>, <postmaster (AT) cjc (DOT) org> wrote:

Quote:

Yes, those fields have indexes on them already.

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.

#5

postmaster (AT) cjc (DOT) org wrote:

Quote:

In article <1158899368.955665.296060 (AT) e3g2000cwe (DOT) googlegroups.com>,
toby <toby (AT) telegraphics (DOT) com.au> wrote:

postmaster (AT) cjc (DOT) org wrote:
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)?

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

Yes, those fields have indexes on them already.

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.