Help me optimise!

------=_NextPart_000_0005_01C4F7FE.5AF4C100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

After splitting my mailinbox folder into a seperate table for each user, =
the performance has improved vastly, but it still takes our one user =
(who has 7000 rows in his mail table) about 30 seconds for his inbox to =
load up in our app.

If the query is cached, it appears in under a second in our app, so the =
bottleneck is definately something in MySQL.

I am really at a loss here, this kind of performance is not acceptable. =
Here is my .ini file, maybe somebody can give me some tips on improving =
performance, our server is a Windows 2003 Server, P4 2.8 Ghz with HT. =
With 140Gb hard disk.It has 1 Gig Ram.. I have read the MySQL optimise =
manual section many times over, but can't really get things any faster. =
This server is not dedicated to MySQL, but 70% of its processing should =
be.

default-character-set=3Dlatin1
default-storage-engine=3DMYISAM
max_connections=3D160
max_allowed_packet=3D64M
query_cache_size=3D48M
table_cache=3D320
tmp_table_size=3D52M
thread_cache=3D8
myisam_max_sort_file_size=3D100G
myisam_max_extra_sort_file_size=3D100G
myisam_sort_buffer_size=3D80M
key_buffer_size=3D148M
read_buffer_size=3D64K
read_rnd_buffer_size=3D256K
sort_buffer_size=3D256K





------=_NextPart_000_0005_01C4F7FE.5AF4C100--

Re: Help me optimise!

Yes, Optimise is run often, but it only knocks off a few seconds. The query
cache is being used nicely, but its the very first time a user views their
mail that bothers me...

What bothers me is, is 7000 rows in 30 seconds normal?

I'm having these problems with 5 users in our company, and we are looking to
serve 50-100 users with our software off 1 server...

----- Original Message -----
From: "Patrick Questembert"
To: "Greg Quinn"
Sent: Tuesday, January 11, 2005 5:13 PM
Subject: RE: Help me optimise!


> Note that MySQL will keep the rows in memory for as long as it still has
> free memory, and the underlying table has not changed. Do you have any way
> to pre-access large inboxes so that they are cached when the user needs
> it?
>
> I assume you have run OPTMIZE of the tables?
>
> Patrick
>
> -----Original Message-----
> From: Greg Quinn [mailto:greg@i-online.co.za]
> Sent: Tuesday, January 11, 2005 9:55 AM
> To: win32@lists.mysql.com
> Subject: Help me optimise!
>
>
> After splitting my mailinbox folder into a seperate table for each user,
> the
> performance has improved vastly, but it still takes our one user (who has
> 7000 rows in his mail table) about 30 seconds for his inbox to load up in
> our app.
>
> If the query is cached, it appears in under a second in our app, so the
> bottleneck is definately something in MySQL.
>
> I am really at a loss here, this kind of performance is not acceptable.
> Here
> is my .ini file, maybe somebody can give me some tips on improving
> performance, our server is a Windows 2003 Server, P4 2.8 Ghz with HT. With
> 140Gb hard disk.It has 1 Gig Ram.. I have read the MySQL optimise manual
> section many times over, but can't really get things any faster. This
> server
> is not dedicated to MySQL, but 70% of its processing should be.
>
> default-character-set=latin1
> default-storage-engine=MYISAM
> max_connections=160
> max_allowed_packet=64M
> query_cache_size=48M
> table_cache=320
> tmp_table_size=52M
> thread_cache=8
> myisam_max_sort_file_size=100G
> myisam_max_extra_sort_file_size=100G
> myisam_sort_buffer_size=80M
> key_buffer_size=148M
> read_buffer_size=64K
> read_rnd_buffer_size=256K
> sort_buffer_size=256K
>
>
>
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Help me optimise!

--=_alternative 005529BF85256F86_=
Content-Type: text/plain; charset="US-ASCII"

"Greg Quinn" wrote on 01/11/2005 10:26:05 AM:

> Yes, Optimise is run often, but it only knocks off a few seconds. The
query
> cache is being used nicely, but its the very first time a user views
their
> mail that bothers me...
>
> What bothers me is, is 7000 rows in 30 seconds normal?
>
> I'm having these problems with 5 users in our company, and we are
looking to
> serve 50-100 users with our software off 1 server...
>

Is that for 7000 whole and complete messages or just 7000 headers? How
much data is in that traffic(KB, MB, GB?)? What does the EXPLAIN for his
query look like?

Bottlenecks are usually either disk i/o, CPU, memory (usu. swap file), or
network. Where is this retrieval getting stuck?

Are your tables defined as MyISAM or InnoDB? (Even if you have every user
in their own table, if you are using InnoDB all of those tables are stored
within a single larger tablespace.)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



> ----- Original Message -----
> From: "Patrick Questembert"
> To: "Greg Quinn"
> Sent: Tuesday, January 11, 2005 5:13 PM
> Subject: RE: Help me optimise!
>
>
> > Note that MySQL will keep the rows in memory for as long as it still
has
> > free memory, and the underlying table has not changed. Do you have any
way
> > to pre-access large inboxes so that they are cached when the user
needs
> > it?
> >
> > I assume you have run OPTMIZE of the tables?
> >
> > Patrick
> >
> > -----Original Message-----
> > From: Greg Quinn [mailto:greg@i-online.co.za]
> > Sent: Tuesday, January 11, 2005 9:55 AM
> > To: win32@lists.mysql.com
> > Subject: Help me optimise!
> >
> >
> > After splitting my mailinbox folder into a seperate table for each
user,
> > the
> > performance has improved vastly, but it still takes our one user (who
has
> > 7000 rows in his mail table) about 30 seconds for his inbox to load up
in
> > our app.
> >
> > If the query is cached, it appears in under a second in our app, so
the
> > bottleneck is definately something in MySQL.
> >
> > I am really at a loss here, this kind of performance is not
acceptable.
> > Here
> > is my .ini file, maybe somebody can give me some tips on improving
> > performance, our server is a Windows 2003 Server, P4 2.8 Ghz with HT.
With
> > 140Gb hard disk.It has 1 Gig Ram.. I have read the MySQL optimise
manual
> > section many times over, but can't really get things any faster. This
> > server
> > is not dedicated to MySQL, but 70% of its processing should be.
> >
> > default-character-set=latin1
> > default-storage-engine=MYISAM
> > max_connections=160
> > max_allowed_packet=64M
> > query_cache_size=48M
> > table_cache=320
> > tmp_table_size=52M
> > thread_cache=8
> > myisam_max_sort_file_size=100G
> > myisam_max_extra_sort_file_size=100G
> > myisam_sort_buffer_size=80M
> > key_buffer_size=148M
> > read_buffer_size=64K
> > read_rnd_buffer_size=256K
> > sort_buffer_size=256K
> >
> >
> >
> >
> >
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 005529BF85256F86_=--

Re: Help me optimise!

--=_alternative 00556AF185256F86_=
Content-Type: text/plain; charset="US-ASCII"

"Greg Quinn" wrote on 01/11/2005 10:31:11 AM:

> Here is the query...
>
> SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority,
> FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,
> FlagFollowUpBy FROM officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f
> WHERE Retained = 0 AND isTempMimeMessage = 0 AND Location = 'Inbox' AND
> UserID = 1
>
> All fields in the conditional clause have indexes, repliedMessages and
> forwardedMessages are the largest fields in the table, both being TEXT
> columns.
>

MySQL will only use one index at a time. You may need to create one or
more multi-column indexes.

What does the EXPLAIN for this query look like? Would you also post the
results of "SHOW CREATE TABLE
officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f\G"?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

> The rest of the fields are varchar, int, tinyint etc.
>
>
> ----- Original Message -----
> From: "Nick Arnett"
> To: "Greg Quinn"
> Sent: Tuesday, January 11, 2005 5:19 PM
> Subject: Re: Help me optimise!
>
>
> > Greg Quinn wrote:
> >> After splitting my mailinbox folder into a seperate table for each
user,
> >> the performance has improved vastly, but it still takes our one user
(who
> >> has 7000 rows in his mail table) about 30 seconds for his inbox to
load
> >> up in our app.
> >>
> >> If the query is cached, it appears in under a second in our app, so
the
> >> bottleneck is definately something in MySQL.
> >>
> >> I am really at a loss here, this kind of performance is not
acceptable.
> >> Here is my .ini file, maybe somebody can give me some tips on
improving
> >> performance, our server is a Windows 2003 Server, P4 2.8 Ghz with HT.

> >> With 140Gb hard disk.It has 1 Gig Ram.. I have read the MySQL
optimise
> >> manual section many times over, but can't really get things any
faster.
> >> This server is not dedicated to MySQL, but 70% of its processing
should
> >> be.
> >
> > What is the query?
> >
> > Nick
> >
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=sgreen@unimin.com
>

--=_alternative 00556AF185256F86_=--

Re: Help me optimise!

Here is the query...

SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority,
FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,
FlagFollowUpBy FROM officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f
WHERE Retained = 0 AND isTempMimeMessage = 0 AND Location = 'Inbox' AND
UserID = 1

All fields in the conditional clause have indexes, repliedMessages and
forwardedMessages are the largest fields in the table, both being TEXT
columns.

The rest of the fields are varchar, int, tinyint etc.


----- Original Message -----
From: "Nick Arnett"
To: "Greg Quinn"
Sent: Tuesday, January 11, 2005 5:19 PM
Subject: Re: Help me optimise!


> Greg Quinn wrote:
>> After splitting my mailinbox folder into a seperate table for each user,
>> the performance has improved vastly, but it still takes our one user (who
>> has 7000 rows in his mail table) about 30 seconds for his inbox to load
>> up in our app.
>>
>> If the query is cached, it appears in under a second in our app, so the
>> bottleneck is definately something in MySQL.
>>
>> I am really at a loss here, this kind of performance is not acceptable.
>> Here is my .ini file, maybe somebody can give me some tips on improving
>> performance, our server is a Windows 2003 Server, P4 2.8 Ghz with HT.
>> With 140Gb hard disk.It has 1 Gig Ram.. I have read the MySQL optimise
>> manual section many times over, but can't really get things any faster.
>> This server is not dedicated to MySQL, but 70% of its processing should
>> be.
>
> What is the query?
>
> Nick
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Help me optimise!

------=_NextPart_000_0049_01C4F805.9A5B0DC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Here is the explain

# EXPLAIN SELECT InBoxMailID, repliedMessages, forwardedMessages, =
thePriority,=20
# FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,=20
# FlagFollowUpBy FROM =
officium_mailinbox_8bbb2deff82145ae9d3b331230a8092c
# WHERE Retained =3D 0 AND isTempMimeMessage =3D 0 AND Location =3D =
'Inbox' AND=20
# UserID =3D 1

'id','select_type','table','type','possible_keys','key','key _len','ref','=
rows','Extra'
'1','SIMPLE','officium_mailinbox_8bbb2deff82145ae9d3b331230a 8092c','ref',=
'IndexIsTempMimeMessage,IndexUserID,indexRetained,IndexLocat ion','IndexLo=
cation','51','const','1379','Using where'

Here is the create...

CREATE TABLE `officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f` (
`InboxMailID` varchar(60) NOT NULL default '',
`UserID` tinyint(4) NOT NULL default '1',
`theFrom` varchar(250) default '',
`theFromEmail` varchar(250) default '',
`ReplyTo` varchar(255) default '',
`theSubject` text,
`RecipientsTo` text,
`RecipientsCC` text,
`RecipientsBCC` text,
`Attachments` text,
`thePriority` tinyint(4) default '0',
`theBody` longtext,
`MessageSource` longtext,
`Location` varchar(50) default '',
`isRead` tinyint(4) default '0',
`receivedDate` datetime default '0000-00-00 00:00:00',
`receivedTime` datetime default '0000-00-00 00:00:00',
`sentDate` datetime default '0000-00-00 00:00:00',
`sentTime` datetime default '0000-00-00 00:00:00',
`ContentType` varchar(100) default '',
`RuleApplied` tinyint(4) default '0',
`isArchived` tinyint(4) default '0',
`RepliedMessages` text,
`ForwardedMessages` text,
`IsNew` tinyint(4) default '0',
`ReadReceiptRead` tinyint(4) default '0',
`ReadReceiptEmail` varchar(250) default '',
`FlagTo` varchar(100) default '',
`FlagFollowUpBy` datetime default '0000-00-00 00:00:00',
`FlagStatus` varchar(20) default '',
`isTempMimeMessage` tinyint(4) default '0',
`Retained` tinyint(4) default '0',
PRIMARY KEY (`InboxMailID`),
KEY `IndexIsTempMimeMessage` (`isTempMimeMessage`),
KEY `IndexUserID` (`UserID`),
KEY `indexRetained` (`Retained`),
KEY `IndexRuleApplied` (`RuleApplied`),
KEY `IndexisRead` (`isRead`),
KEY `IndexLocation` (`Location`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;=20
----- Original Message -----=20
From: SGreen@unimin.com=20
To: Greg Quinn=20
Cc: win32@lists.mysql.com=20
Sent: Tuesday, January 11, 2005 5:30 PM
Subject: Re: Help me optimise!




"Greg Quinn" wrote on 01/11/2005 10:31:11 AM:

> Here is the query...
>=20
> SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority, =

> FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,=20
> FlagFollowUpBy FROM =
officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f=20
> WHERE Retained =3D 0 AND isTempMimeMessage =3D 0 AND Location =3D =
'Inbox' AND=20
> UserID =3D 1
>=20
> All fields in the conditional clause have indexes, repliedMessages =
and=20
> forwardedMessages are the largest fields in the table, both being =
TEXT=20
> columns.
>=20

MySQL will only use one index at a time. You may need to create one or =
more multi-column indexes. =20

What does the EXPLAIN for this query look like? Would you also post =
the results of "SHOW CREATE TABLE =
officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f\G"?=20

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=20

> The rest of the fields are varchar, int, tinyint etc.
>=20
>=20
> ----- Original Message -----=20
> From: "Nick Arnett"
> To: "Greg Quinn"
> Sent: Tuesday, January 11, 2005 5:19 PM
> Subject: Re: Help me optimise!
>=20
>=20
> > Greg Quinn wrote:
> >> After splitting my mailinbox folder into a seperate table for =
each user,=20
> >> the performance has improved vastly, but it still takes our one =
user (who=20
> >> has 7000 rows in his mail table) about 30 seconds for his inbox =
to load=20
> >> up in our app.
> >>
> >> If the query is cached, it appears in under a second in our app, =
so the=20
> >> bottleneck is definately something in MySQL.
> >>
> >> I am really at a loss here, this kind of performance is not =
acceptable.=20
> >> Here is my .ini file, maybe somebody can give me some tips on =
improving=20
> >> performance, our server is a Windows 2003 Server, P4 2.8 Ghz with =
HT.=20
> >> With 140Gb hard disk.It has 1 Gig Ram.. I have read the MySQL =
optimise=20
> >> manual section many times over, but can't really get things any =
faster.=20
> >> This server is not dedicated to MySQL, but 70% of its processing =
should=20
> >> be.
> >
> > What is the query?
> >
> > Nick
> >=20
>=20
>=20
>=20
> --=20
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: =
http://lists.mysql.com/win32?unsub=3Dsgreen@unimin.com
>=20

------=_NextPart_000_0049_01C4F805.9A5B0DC0--

Re : Help me optimise!

--=======AVGMAIL-41E5290542D3=======
Content-Type: multipart/alternative; boundary="----=_NextPart_000_000C_01C4F8BD.2CBC1E90"

------=_NextPart_000_000C_01C4F8BD.2CBC1E90
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Nick,

I actually only saw your mail now, just as I posted my last attempt mail =
to the list.

>Your join type is "ref," which is not good for getting a large number =
of=20
>rows. It is using the IndexLocation key and thinks it has to look at=20
>1379 rows to run the query.

I have created a multi-column index, in the order the conditions are =
specified in my query.

I was under the impression that a table scan would slow things down for =
large tables, i.e when join_type is ALL.


>> tinyint(1) unsigned not null

Thanks, I will make this modification to the table

With regard to de-normalising the tables and putting data into seperate =
tables, are you saying I should put the BODY and MESSAGE SOURCE and =
other big fields into their own table? As they are not used in my query? =
Should I make this table INNODb?

I did remove the big MESSAGESOURCE field from one of my test tables, but =
the performance is still about the same... I also removed two of the =
TEXT fields from the query, i.e I used '' as RepliedMessages, '' as =
ForwardedMessages, but no performance improvement. Will I start seeing =
performance if I totally remove all these text fields from the table?

Is myISAM the best table type for what I am doing with the message =
headers?=20

Thanks for the help..





Greg Quinn wrote:
> Here is the explain
>=20
> # EXPLAIN SELECT InBoxMailID, repliedMessages, forwardedMessages, =
thePriority,=20
> # FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,=20
> # FlagFollowUpBy FROM =
officium_mailinbox_8bbb2deff82145ae9d3b331230a8092c
> # WHERE Retained =3D 0 AND isTempMimeMessage =3D 0 AND Location =3D =
'Inbox' AND=20
> # UserID =3D 1
>=20
> =
'id','select_type','table','type','possible_keys','key','key _len','ref','=
rows','Extra'
> =
'1','SIMPLE','officium_mailinbox_8bbb2deff82145ae9d3b331230a 8092c','ref',=
'IndexIsTempMimeMessage,IndexUserID,indexRetained,IndexLocat ion','IndexLo=
cation','51','const','1379','Using where'

Your join type is "ref," which is not good for getting a large number of =

rows. It is using the IndexLocation key and thinks it has to look at=20
1379 rows to run the query.

For one thing, run ANALYZE TABLE on the table if you haven't recently.

I suspect that the main thing you need to do is create an appropriate=20
multicolumn index, using the columns that are most often queried=20
together (and the most often used one first). For example, I suspect=20
that an index of (Retained, isTempMimeMessage, Location, UserID) would=20
speed things up. However, the best performance depends on the=20
distribution of your data, so this may not be quite right.

Keep in mind that (until 5.0 comes along), MySQL can only use one index=20
per table. Thus, if you're often querying against the same handful of=20
columns, a multi-column index for them usually makes sense.

It looks like some of your columns are for Booleans (RuleApplied, isNew, =

ReadReceiptRead, Retained... etc.) If so, this definition will be =
better:

tinyint(1) unsigned not null

For those that aren't Booleans (thePriority?) an enum column might be=20
better.

Your text and longtext columns may cause a performance hit. Even though=20
it violates normalization, consider putting them in a separate table and =

using joins to get them when needed. That's how I manage tables that=20
contain millions of e-mail and other messages. All the metadata is in=20
Innodb tables with VARCHAR, INT and ENUM columns; the headers and body=20
are in MyISAM tables (for the full-text search, partly) with VARCHARs=20
for the message_id as the primary key and text columns for the headers=20
and body. I extract important values from the headers and store them in=20
VARCHAR columns in the metadata table.

I've tried using fixed-length columns in my metadata tables, but I think =

all the wasted space hurt performance more than I gained.

------=_NextPart_000_000C_01C4F8BD.2CBC1E90
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Help me optimise!

--=======AVGMAIL-41E53526442C=======
Content-Type: multipart/alternative; boundary="----=_NextPart_000_0074_01C4F8C4.6745C280"

------=_NextPart_000_0074_01C4F8C4.6745C280
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

Ok, I have optimised the table as much as possible, but the query still =
takes 22 seconds...

CREATE TABLE `officium_mailinbox_698bd9ca09ff43e18a6d58b756b53c3f` (
`InboxMailID` varchar(60) NOT NULL default '',
`UserID` tinyint(4) unsigned NOT NULL default '1',
`theFrom` varchar(250) default '',
`theFromEmail` varchar(250) default '',
`ReplyTo` varchar(255) default '',
`theSubject` varchar(255) default '',
`RecipientsTo` varchar(50) default '',
`Attachments` tinyint(3) unsigned default '0',
`thePriority` tinyint(4) unsigned default '0',
`Location` varchar(15) default '',
`isRead` tinyint(4) unsigned default '0',
`receivedDate` datetime default '0000-00-00 00:00:00',
`receivedTime` datetime default '0000-00-00 00:00:00',
`ContentType` varchar(100) default '',
`RuleApplied` tinyint(4) unsigned default '0',
`isArchived` tinyint(4) unsigned default '0',
`RepliedMessages` tinyint(3) unsigned default '0',
`ForwardedMessages` tinyint(3) unsigned default '0',
`IsNew` tinyint(4) unsigned default '0',
`ReadReceiptRead` tinyint(4) unsigned default '0',
`ReadReceiptEmail` varchar(250) default '',
`FlagTo` varchar(100) default '',
`FlagFollowUpBy` datetime default '0000-00-00 00:00:00',
`FlagStatus` varchar(20) default '',
`isTempMimeMessage` tinyint(4) unsigned default '0',
`Retained` tinyint(4) unsigned default '0',
PRIMARY KEY (`InboxMailID`),
KEY `indexRetained` (`Retained`,`isTempMimeMessage`,`Location`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;=20

----- Original Message -----=20
From: Ian Gibbons=20
To: Greg Quinn=20
Sent: Wednesday, January 12, 2005 3:58 PM
Subject: Re: Help me optimise!




Hi Greg,


I am by no means an expert on optimising MySQL but I can see a few =
problems with your table structure ( comments in line ):




> CREATE TABLE `officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f` (
> `InboxMailID` varchar(60) NOT NULL default '',
> `UserID` tinyint(4) NOT NULL default '1',
> `theFrom` varchar(250) default '',
> `theFromEmail` varchar(250) default '',
> `ReplyTo` varchar(255) default '',
> `theSubject` text,
> `RecipientsTo` text,
> `RecipientsCC` text,
> `RecipientsBCC` text,
> `Attachments` text,
> `thePriority` tinyint(4) default '0',
> `theBody` longtext,
> `MessageSource` longtext,
> `Location` varchar(50) default '',
> `isRead` tinyint(4) default '0',
> `receivedDate` datetime default '0000-00-00 00:00:00',
> `receivedTime` datetime default '0000-00-00 00:00:00',
> `sentDate` datetime default '0000-00-00 00:00:00',
> `sentTime` datetime default '0000-00-00 00:00:00',


You seam to have four separate fields for two dates, why not just =
have:


`receivedDate` datetime default '0000-00-00 00:00:00',


and drop recievedTime as the time is include above. Same with the =
sentDate.




Also you seem to have the messageSource in the same table. =20
It would speed up your application a lot by separating this into =
another table.


> `ContentType` varchar(100) default '',
> `RuleApplied` tinyint(4) default '0',
> `isArchived` tinyint(4) default '0',
> `RepliedMessages` text,
> `ForwardedMessages` text,
> `IsNew` tinyint(4) default '0',
> `ReadReceiptRead` tinyint(4) default '0',
> `ReadReceiptEmail` varchar(250) default '',
> `FlagTo` varchar(100) default '',
> `FlagFollowUpBy` datetime default '0000-00-00 00:00:00',
> `FlagStatus` varchar(20) default '',
> `isTempMimeMessage` tinyint(4) default '0',
> `Retained` tinyint(4) default '0',
> PRIMARY KEY (`InboxMailID`),
> KEY `IndexIsTempMimeMessage` (`isTempMimeMessage`),
> KEY `IndexUserID` (`UserID`),
> KEY `indexRetained` (`Retained`),
> KEY `IndexRuleApplied` (`RuleApplied`),
> KEY `IndexisRead` (`isRead`),
> KEY `IndexLocation` (`Location`)
> )=20


Your keys appear to be set up wrong.


Consider the query:


SELECT InBoxMailID, repliedMessages, forwardedMessages, thePriority,=20
FlagStatus, Attachments, theFrom, theSubject, receivedDate, isRead,=20
FlagFollowUpBy FROM =
officium_mailinbox_03d1d4abfa404b6f945e529bddc7f46f WHERE Retained =3D 0 =
AND isTempMimeMessage =3D 0 AND Location =3D 'Inbox' AND=20
UserID =3D 1=20


You need an index on Retained AND isTempMimeMessage AND location AND =
UserID, your indexes appear to be on individual fields.


I also suggest you repost your question to the mysql list not just the =
win32 list as there is ALOT more expertise there. Not everyone reads =
the win32 list


Hope this helps


Ian
--=20






------------------------------------------------------------ -------------=
-----


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.10 - Release Date: 1/10/2005

------=_NextPart_000_0074_01C4F8C4.6745C280
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Re : Help me optimise!

> It looks like some of your columns are for Booleans (RuleApplied, isNew,
> ReadReceiptRead, Retained... etc.) If so, this definition will be better:
>
> tinyint(1) unsigned not null
>
> For those that aren't Booleans (thePriority?) an enum column might be
> better.

Sorry to hi-jack the thread like this, but why use a tinyint over a
boolean (linkage?)

G.


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Re : Help me optimise!

--=_alternative 005B248185256FA4_=
Content-Type: text/plain; charset="US-ASCII"

news wrote on 02/10/2005 08:48:38 AM:

> > It looks like some of your columns are for Booleans (RuleApplied,
isNew,
> > ReadReceiptRead, Retained... etc.) If so, this definition will be
better:
> >
> > tinyint(1) unsigned not null
> >
> > For those that aren't Booleans (thePriority?) an enum column might be
> > better.
>
> Sorry to hi-jack the thread like this, but why use a tinyint over a
> boolean (linkage?)
>
> G.
>

In the current versions of MySQL, BOOLEAN is mapped to TINYINT(1). So,
even if you delcare a column as BOOLEAN, you will see it created as
TINYINT(1). There should be plans to implement a BIT datatype (to which
BOOLEAN could map instead) but I can't say when it will be ready for use.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


--=_alternative 005B248185256FA4_=--