I'm just guessing here, but I suppose most of the char(1) fields are y/n
fields. If that's the case you should consider using a single BIGINT and
flag those bits instead. That would considerably reduce the size of the
table on one hand and I guess it should improve things speed-wise as well.
Obvi
Greer, Darren (MED) writes:
> If I move the status to another table, and then wanted to get a count of
> everyone who is of status 'A', how would that be any quicker? Would I
> join the tables?
Communication error - reattempting.
I meant that if you want to get the count of the number of 'A's
My suggestion (using a bigint and flagging the bits instead of using 40
char(1) fields) is only valid if you don't use any of those fields in WHERE
clauses in SELECT's. Not that you wouldn't be able to retrieve the entries
based on the respective field, but I don't think it would be indeed faster
PROTECTED]
Subject: Re: General Query time question
I didn't think/read far enough. One more attempt is due.
> > Correction on the query: SELECT count(*) as count FR
> OM userdata WHERE
> > status = 'A';
I didn't see that it was on that column you had an index
I didn't think/read far enough. One more attempt is due.
> > Correction on the query: SELECT count(*) as count FR
> OM userdata WHERE
> > status = 'A';
I didn't see that it was on that column you had an index,
so forget I said 'of course'. Have you used EXPLAIN to
figure out if the index is act
ogdan Stancescu [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 08, 2002 10:37 AM
To: Greer, Darren (MED)
Cc: '[EMAIL PROTECTED]'
Subject: Re: General Query time question
I'm just guessing here, but I suppose most of the char(1) fields are y/n
fields. If that's the case yo
Greer, Darren (MED) writes:
> Correction on the query: SELECT count(*) as count FR
OM userdata WHERE
> status = 'A';
Ah. That does make a difference. MySQL is of course
forced to go through all of your data, counting the
number of rows with status 'A'. I don't believe an
index would do you much
Correction on the query: SELECT count(*) as count FROM userdata WHERE
status = 'A';
Sorry.
-Original Message-
From: Greer, Darren (MED)
Sent: Tuesday, January 08, 2002 10:20 AM
To: '[EMAIL PROTECTED]'
Subject: General Query time question
Hello all. I have a data
Hello all. I have a database which is around 10million rows. The
structure is as follows:
id int(11) NOT NULL- Auto Increment
address char(90) NOT NULL
status char(2) NOT NULL
country char(2)
state char(2)
areacode char(3)
...about 40 char(1) fields.
I have an unique index on address,
