[geeklog-devtalk] geeklog-devel digest, Vol 1 #505 - 6 msgs

geeklog-devel-request at lists.geeklog.net geeklog-devel-request at lists.geeklog.net
Wed Feb 9 13:00:02 EST 2005

Previous message: [geeklog-devtalk] trackbacks
Next message: [geeklog-devtalk] geeklog-devel digest, Vol 1 #506 - 7 msgs
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Send geeklog-devel mailing list submissions to
geeklog-devel at lists.geeklog.net

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.geeklog.net/listinfo/geeklog-devel
or, via email, send a message with subject or body 'help' to
geeklog-devel-request at lists.geeklog.net

You can reach the person managing the list at
geeklog-devel-admin at lists.geeklog.net

When replying, please edit your Subject line so it is more specific
than "Re: Contents of geeklog-devel digest..."

Today's Topics:

1. Spamx Mass Delete (Tom Willett)
2. MySQL performance - Use of Indexes (Blaine Lang)
3. SpamAssassin WG on blog spam (Dirk Haun)
4. Re: MySQL performance - Use of Indexes (Tony Bibbs)
5. Re: MySQL performance - Use of Indexes (Dwight Trumbower)
6. Re: MySQL performance - Use of Indexes (Tony Bibbs)

--__--__--

Message: 1
Date: Tue, 08 Feb 2005 13:44:10 -0500
From: Tom Willett <tomw at pigstye.net>
To: "[Geeklog-Deve]" <geeklog-devel at lists.geeklog.net>
Subject: [geeklog-devel] Spamx Mass Delete
Reply-To: geeklog-devel at lists.geeklog.net

This is a multi-part message in MIME format.
--------------060806020302070504050001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Noticed that on Geeklog.net some were complaining that on a new install
MassDelete didn't work. Looked into it and noticed that it was relying
on old data in the spamx table. I updated the module to the new reality
and have attached it.

--

Tom Willett
tomw at pigstye.net

--------------060806020302070504050001
Content-Type: application/x-compressed;
name="MassDelete.tgz"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
filename="MassDelete.tgz"

H4sIAOwGCUIAA+1YbU/bSBDma/wrplZUJ22aOBC404WkpJBySFAQoWolhCLH2SQ+/FbvuhDR
/Peb2bUdh4RCpUqnkzyitnd33nZmdvZJzyzOj5jLBKv3xp7j120XZ+rhLNz6bWQ2TfOPVmvL
JNozV9+42Nrb22qa2+b2trnT3Mb55m6ztbsF5u9z4WmKubAigC0rtOwZe5rvufX/Ke2/x1Rr
jTdvtDcwcVz2F8DZkyWBPLQIY7kKduB5zBfAQ8vTSMFhEM4jZzoTUDmswrZptmA0BzFjMAlc
N7hz/ClYsZgFEf9LChxATw5LpavAgy+Oi3pFqSQC7+4gdKZczFndZ0Lynjo28zkbQ+yPWQTH
nz7DMfNZZLlwEY9cx045iLuhaRH7FjsRGwa+zSrl4eH5p4/XRmiJmXEDdTBCN546Pm+Q8/eN
DxZnjzZrVNuapslhLiTA7gXzxxwyCXjQShS/Eu3f5yKKbRFENMR/Da00iX1bOIEPY4eHrjWv
VFGgNHWDEXqu/Krh++J8cEXvq96H0/4Av057n46Hg6+m2UZ2/Csn8tDJrV0bHro2Y9bYuEF3
kcuyBXFIfdeGJU3TGi65Xn7JdTxHJFLOBCoVJbqqXWWaYmTcVOH1a6iQlq5ZrdK2UacfezYq
lU5KNehmhBMHQYhhcqJnI2+kqkp3M6w/dIPKEDVEuCmpAB/I8wo9m1guZym7tObwIbE/bwWn
pGLUpKQfkrdUw+MRJk6Zrr1r/lmlOBj1/r2FCWb5mkiMQ0KlsvBCdBalhxHD9GCt6etyek3X
a8p+OzFbKg/Is2HCe33TIVXt1KuFlnupp+0GWP5pRKQeuVCOGI9dSu3Rh+G3mEXzij7on/YP
rwDPdIQH6yEtq2sjObPcuFnA+eVR/5JO6JGFhX3UHxzC6cnZyRVQjnVloexHwR1XujHXlzio
JAYVwyRAh5xOs1129juKGz/fvk0jVe51UHTChD3rRRGWf146DSMqYdheK6sxAYtDOfnOsi6j
6Fse66RLlO0NqWprWZLSD0o1iQ/ZvcMFf3nVkD1Zp5Ajqbb0S21mVWFaCuX+Vwywz+6y3WYr
GCs6sv2v77rsntmxQBu9LIt4Jtu5KlbcHWhmwSqVRhjY2/bTRbVZrixmDn/XxdOfWFJWHewy
2Jnwky8/xTxkS0fShqDeb6HZXhpT1Zq2sXrKVF/pOGgQzaqOtQCGBz7pNDk5Yx8LxgOP4d0x
7uhhwIUOqtV1dBnjJA/cEWxoUZcexpGrMtJYTYmDt8k9Fcx72qrljzvLZq93jfaazzlf0X0R
ICqwb6Vq/bU/4mFbPuVDJ3uwz1EZtlZZtLpsu5sUG/tBKO+J75YbUwPUm6bebZr7DTXfXVnv
6LumDkozG3d3l2wv05wXxtFTRvAO17vb5q9p3yGhnaeVtmi99YzShnLvuQwEgjVVsfyUZ/sF
PDsv4Gm9gGf3BTx7G3iMfccPYwF0oCiMg3hEpZLUjSpvPY2h8ejY5C9qqrrNFdagc6NWFgpV
REzEkQ8pm8IauJbDLa7j31aqWikPW1bASaJDl9gwgUnUyBEPqctGb0udCUpSHBwsmDrfmZ/C
SFqquM4EK1LdWsk8nc0qLR6EVoRKqY8j6EypjF2J3oktODla5cWbmWCn4uWK9+QIggkEo3/o
WKYodsTcwJ9yEMFPFMjcLI3JYYXVp3WwIuHYiB4gRKhbA7zvlNNJcFb1XMpJns75jI1x1yJA
0IN3O3kVWlMmJySclsAC1T1GlFl/xiaOcajJHdaUmwR01IW1DjYzkDn8POhfEgSUu0Nfsbyw
9gwjnUtAFnY6FiHElmYSIEiB74IpR6+YF4o5TvJkOZuQrtDFsryOGw0IfHeeRp5TMmQkjSSI
BmAfxrsTI4mfEYMZjl2MEGKVY8Zu3WC61Kawqzo0naWKKvz4Abl5qWzVkUx8k/TqXZ9gPWsk
oekSTnGE+g7j8jQ/5i87Yzq6FE66Lh/hkexuW4MUG8yQ84jsOGVdGlsXWhr7lhnLXFqs2l5H
jJBBxuAOf1MNsYqmURCH9BGyyBvKafUpF9Snx7wRi7gaWD6W5MfL8zPEm3ILC/jyd/+yj0Pl
3IK8e6ASWRgEL1d96il3lkgRclAxv2HFHmL1SYEpEyeCeci9AeTWMPEYjhrotuQ3qGql8TWN
qMrGOsNj93NNZHk5oOPQXisV1JX8Rn5Kl50X1/K5InpxUapYZAdJBsQOYtkQNhvmyjDf5PfT
ccjqnFxP1dWWljcoXtnQYlmPa4UvZ7EnDBijjSt4BneO6ybnHgiNrlVw0qouTo+Hiu1CCh5m
DZHip0KM3ql00G/63KHA+3Fw0Tv7OsSGUsnfpnSPKomxgrYJ/0JbaNr7rvZf/69NQQUVVFBB
BRVUUEEFFVRQQQUVVFBBBRVU0PP0L9zun1sAKAAA
--------------060806020302070504050001--

--__--__--

Message: 2
From: "Blaine Lang" <geeklog at langfamily.ca>
To: <geeklog-devel at lists.geeklog.net>
Date: Tue, 8 Feb 2005 15:52:33 -0500
Subject: [geeklog-devel] MySQL performance - Use of Indexes
Reply-To: geeklog-devel at lists.geeklog.net

This is a multi-part message in MIME format.

------=_NextPart_000_01FA_01C50DF6.342F7B10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

This was new for me today and thought that I would share as it may also =
effect some GL SQL queries.

I have a client project with 3.2M records which is actually only 30 days =
of data :)
A query that I have was like this:
SELECT * FROM calllogs WHERE=20
StartTime < '2005-02-02 11:02:19' AND=20
(Source_Caller_ID=3D'4162389001' OR Dest_Caller_ID=3D'4162389001')=20
ORDER BY StartTime

All three fields are indexed fields. The query was taking 90 seconds to =
complete.
But if you ran each query for Source_Caller_ID and Dest_Caller_ID =
separate ( so instead of the OR ), each query would complete in under 1 =
second.

Changing the query to use 2 selects and a UNION resulted in a time of =
about 1 sec to complete.

SELECT * FROM calllogs WHERE Source_Caller_ID=3D'4162389001'
UNION
SELECT * FROM calllogs WHERE Dest_Caller_ID=3D'4162389001'=20
Order By startTime;

Reason: MySQL ONLY uses the first index and was then in effect doing =
tablescan's when the OR condition and second field was added.

Blaine
------=_NextPart_000_01FA_01C50DF6.342F7B10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2523" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>This was new for me today and thought =
that I would=20
share as it may also effect some GL SQL queries.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I have a client project with 3.2M =
records which is=20
actually only 30 days of data :)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>A query that I have was like =
this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>SELECT * FROM calllogs WHERE =
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    StartTime < =
'2005-02-02=20
11:02:19' AND </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    =
(Source_Caller_ID=3D'4162389001'=20
OR Dest_Caller_ID=3D'4162389001') </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    ORDER BY =
StartTime</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>All three fields are indexed fields. =
The query was=20
taking 90 seconds to complete.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>But if you ran each query for =
Source_Caller_ID and=20
Dest_Caller_ID separate ( so instead of the OR ), each query would =
complete in=20
under 1 second.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Changing the query to use 2 selects and =
a UNION=20
resulted in a time of about 1 sec to complete.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>SELECT *</FONT><FONT face=3DArial =
size=3D2> FROM=20
calllogs</FONT><FONT face=3DArial size=3D2> WHERE=20
Source_Caller_ID=3D'4162389001'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    UNION</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    SELECT =
* FR</FONT><FONT=20
face=3DArial size=3D2>OM calllogs</FONT><FONT face=3DArial =
size=3D2> WHERE=20
Dest_Caller_ID=3D'4162389001' </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>    Order By =
startTime;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Reason: MySQL ONLY uses the first index =
and was=20
then in effect doing tablescan's when the OR condition and second field =
was=20
added.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Blaine</FONT></DIV></BODY></HTML>

------=_NextPart_000_01FA_01C50DF6.342F7B10--

--__--__--

Message: 3
From: "Dirk Haun" <dirk at haun-online.de>
To: <geeklog-devel at lists.geeklog.net>
Date: Tue, 8 Feb 2005 21:56:10 +0100
Organization: Terra Software Systems
Subject: [geeklog-devel] SpamAssassin WG on blog spam
Reply-To: geeklog-devel at lists.geeklog.net

FYI: The BlogSpamAssassin project is a SpamAssassin working group
focussing on spam in blogs (comment spam, etc.). So far, they have this
page <http://wiki.apache.org/spamassassin/BlogSpamAssassin> and a (quiet)
mailing list.

I'll keep an eye on it ...

bye, Dirk

--
http://www.haun-online.de/
http://www.haun.info/

--__--__--

Message: 4
Date: Tue, 08 Feb 2005 15:05:25 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] MySQL performance - Use of Indexes
Reply-To: geeklog-devel at lists.geeklog.net

That's ridiculous. I tell you, I see what Postgres has been doing (most
recently, the addition of table spaces) and I sometimes wonder why I use
MySQL. Sure it's easy to learn, fast, etc but man do I miss triggers,
sp's etc.

This opens up the option of us wanting to review all our SQL in 1.3.x
and seeing if it may be effected by this. The search page comes to mind.

--Tony

Blaine Lang wrote:

> This was new for me today and thought that I would share as it may

> also effect some GL SQL queries.

>

> I have a client project with 3.2M records which is actually only 30

> days of data :)

> A query that I have was like this:

> SELECT * FROM calllogs WHERE

> StartTime < '2005-02-02 11:02:19' AND

> (Source_Caller_ID='4162389001' OR Dest_Caller_ID='4162389001')

> ORDER BY StartTime

>

> All three fields are indexed fields. The query was taking 90 seconds

> to complete.

> But if you ran each query for Source_Caller_ID and Dest_Caller_ID

> separate ( so instead of the OR ), each query would complete in under

> 1 second.

>

> Changing the query to use 2 selects and a UNION resulted in a time of

> about 1 sec to complete.

>

> SELECT * FROM calllogs WHERE Source_Caller_ID='4162389001'

> UNION

> SELECT * FROM calllogs WHERE Dest_Caller_ID='4162389001'

> Order By startTime;

>

> Reason: MySQL ONLY uses the first index and was then in effect doing

> tablescan's when the OR condition and second field was added.

>

> Blaine

--__--__--

Message: 5
Date: Tue, 8 Feb 2005 15:51:02 -0600 (CST)
Subject: Re: [geeklog-devel] MySQL performance - Use of Indexes
From: "Dwight Trumbower" <dwight at trumbower.com>
To: geeklog-devel at lists.geeklog.net
Reply-To: geeklog-devel at lists.geeklog.net

OR are usually always timely in any DB. Just like using IN or NOT IN. In
other DBs you can usually run a query plan to find this information out. I
haven't heard if MySQL has that capability yet.

You just were reminded that MYSQL was never designed to be a "true" RDMS.
It was great for read/reporting type solutions.

Postgres latest release is suppose to be really nice. I want to look at it
for future projects, but it won't be a while before I can look at it.
Unless someone pays me. :)

> That's ridiculous. I tell you, I see what Postgres has been doing (most

> recently, the addition of table spaces) and I sometimes wonder why I use

> MySQL. Sure it's easy to learn, fast, etc but man do I miss triggers,

> sp's etc.

>

> This opens up the option of us wanting to review all our SQL in 1.3.x

> and seeing if it may be effected by this. The search page comes to mind.

>

> --Tony

>

> Blaine Lang wrote:

>

>> This was new for me today and thought that I would share as it may

>> also effect some GL SQL queries.

>>

>> I have a client project with 3.2M records which is actually only 30

>> days of data :)

>> A query that I have was like this:

>> SELECT * FROM calllogs WHERE

>> StartTime < '2005-02-02 11:02:19' AND

>> (Source_Caller_ID='4162389001' OR Dest_Caller_ID='4162389001')

>> ORDER BY StartTime

>>

>> All three fields are indexed fields. The query was taking 90 seconds

>> to complete.

>> But if you ran each query for Source_Caller_ID and Dest_Caller_ID

>> separate ( so instead of the OR ), each query would complete in under

>> 1 second.

>>

>> Changing the query to use 2 selects and a UNION resulted in a time of

>> about 1 sec to complete.

>>

>> SELECT * FROM calllogs WHERE Source_Caller_ID='4162389001'

>> UNION

>> SELECT * FROM calllogs WHERE Dest_Caller_ID='4162389001'

>> Order By startTime;

>>

>> Reason: MySQL ONLY uses the first index and was then in effect doing

>> tablescan's when the OR condition and second field was added.

>>

>> Blaine

>

>

> _______________________________________________

> geeklog-devel mailing list

> geeklog-devel at lists.geeklog.net

> http://lists.geeklog.net/listinfo/geeklog-devel

>

--
Dwight Trumbower

--__--__--

Message: 6
Date: Tue, 08 Feb 2005 16:30:19 -0600
From: Tony Bibbs <tony at tonybibbs.com>
To: geeklog-devel at lists.geeklog.net
Subject: Re: [geeklog-devel] MySQL performance - Use of Indexes
Reply-To: geeklog-devel at lists.geeklog.net

I'd argue against "that MYSQL was never designed to be a 'true' RDMS"

Their stance has always been to slowly add features so as to make
performance and stability a priority. MySQL 5.x will add most of what I
feel I need as a developer.

My main gripe is mainly that they sure do take their sweet time getting
those badly desired features done. I've got the new Postgres
installed. IMHO, it's harder to administer than MySQL but most of my
issues are covered in the documentation adequately.

--Tony

Dwight Trumbower wrote:

>OR are usually always timely in any DB. Just like using IN or NOT IN. In

>other DBs you can usually run a query plan to find this information out. I

>haven't heard if MySQL has that capability yet.

>

>You just were reminded that MYSQL was never designed to be a "true" RDMS.

>It was great for read/reporting type solutions.

>

>Postgres latest release is suppose to be really nice. I want to look at it

>for future projects, but it won't be a while before I can look at it.

>Unless someone pays me. :)

>

>

>

>

>

>

>>That's ridiculous. I tell you, I see what Postgres has been doing (most

>>recently, the addition of table spaces) and I sometimes wonder why I use

>>MySQL. Sure it's easy to learn, fast, etc but man do I miss triggers,

>>sp's etc.

>>

>>This opens up the option of us wanting to review all our SQL in 1.3.x

>>and seeing if it may be effected by this. The search page comes to mind.

>>

>>--Tony

>>

>>Blaine Lang wrote:

>>

>>

>>

>>>This was new for me today and thought that I would share as it may

>>>also effect some GL SQL queries.

>>>

>>>I have a client project with 3.2M records which is actually only 30

>>>days of data :)

>>>A query that I have was like this:

>>>SELECT * FROM calllogs WHERE

>>> StartTime < '2005-02-02 11:02:19' AND

>>> (Source_Caller_ID='4162389001' OR Dest_Caller_ID='4162389001')

>>> ORDER BY StartTime

>>>

>>>All three fields are indexed fields. The query was taking 90 seconds

>>>to complete.

>>>But if you ran each query for Source_Caller_ID and Dest_Caller_ID

>>>separate ( so instead of the OR ), each query would complete in under

>>>1 second.

>>>

>>>Changing the query to use 2 selects and a UNION resulted in a time of

>>>about 1 sec to complete.

>>>

>>>SELECT * FROM calllogs WHERE Source_Caller_ID='4162389001'

>>> UNION

>>> SELECT * FROM calllogs WHERE Dest_Caller_ID='4162389001'

>>> Order By startTime;

>>>

>>>Reason: MySQL ONLY uses the first index and was then in effect doing

>>>tablescan's when the OR condition and second field was added.

>>>

>>>Blaine

>>>

>>>

>>_______________________________________________

>>geeklog-devel mailing list

>>geeklog-devel at lists.geeklog.net

>>http://lists.geeklog.net/listinfo/geeklog-devel

>>

>>

>>

>

>

>

>

--__--__--

_______________________________________________
geeklog-devel mailing list
geeklog-devel at lists.geeklog.net
http://lists.geeklog.net/listinfo/geeklog-devel

End of geeklog-devel Digest

Previous message: [geeklog-devtalk] trackbacks
Next message: [geeklog-devtalk] geeklog-devel digest, Vol 1 #506 - 7 msgs
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

More information about the geeklog-devtalk mailing list