Hi Elazar,
It looks to me that to speed things up you will need to re-design your
database in order to match the expected queries. In particular, you will
need to keep the table ordered by the date field which should be a key
field. You should probably split the table into several tables by
category_name. You might consider having separate tables whose rows are
just dates and categories and the corresponding keys to the articles
table.
- yba
On Wed, 21 Mar 2007, Elazar Leibovich wrote:
Date: Wed, 21 Mar 2007 16:26:14 +0200
From: Elazar Leibovich <[EMAIL PROTECTED]>
To: Jonathan Ben Avraham <[EMAIL PROTECTED]>
Cc: ILUG <linux-il@linux.org.il>
Subject: Re: [Job] MySQL consultation
This is not the problem. The problem is that running Queries like:
SELECT SQL_CALC_FOUND_ROWS c.cat_ID FROM
posts AS l,post2cat AS a2c,
categories AS c
WHERE (date>2006-01-01 AND date<2006-01-02) AND(c.cat_ID=a2c.category_id AND
l.article_id=a2c.post_id)
AND (c.category_name="cat1" OR category_name="cat2")
GROUP BY l.id
HAVING count(a2c.rel_id)=%s
LIMIT 10;
Takes about 10 minutes to complete. I want to run many such queries, and I'm
not sure if I can optimize it, or that since the DB is huge, I've met the
SQL limit and I should start looking in other directions (reducing logs
resolution etc.).
Will the fact I'll have no TEXT columns speed things up? Will removing all
columns except of id from the giant posts table throttle things up
significantly?
On 3/21/07, Jonathan Ben Avraham <[EMAIL PROTECTED]> wrote:
On Wed, 21 Mar 2007, Elazar Leibovich wrote:
> Date: Wed, 21 Mar 2007 16:02:22 +0200
> From: Elazar Leibovich <[EMAIL PROTECTED]>
> To: Jonathan Ben Avraham <[EMAIL PROTECTED] >
> Cc: linux-il@linux.org.il
> Subject: Re: [Job] MySQL consultation
>
> The whole table. SELECT COUNT(*) FROM tbl WHERE id>1;
> Thanks for the speedy reply.
Hi Elazar,
One to three minutes seems entirely reasonable to me, depending on
hardware.
You should probably be able to update a count of records accurately each
time you add to, or delete from, the table instead of using COUNT(*) to
re-count the *whole* table if you need to save time.
- yba
>
> On 3/21/07, Jonathan Ben Avraham <[EMAIL PROTECTED]> wrote:
>> Hi Elazar,
>> What is it exactly that you are counting?
>>
>> - yba
>>
>>
>> On Wed, 21 Mar 2007, Elazar Leibovich wrote:
>>
>> > Date: Wed, 21 Mar 2007 15:42:21 +0200
>> > From: Elazar Leibovich < [EMAIL PROTECTED]>
>> > To: linux-il@linux.org.il
>> > Subject: Re: [Job] MySQL consultation
>> >
>> > It seems that my question boils down to this:
>> > I'm having a 3Gb table with three TEXT columns and an ID.
>> > Counting it takes between 1 to 2 minutes, is it normal for such a
size
>> > of a table?
>> >
>> > On 3/21/07, Elazar Leibovich <[EMAIL PROTECTED]> wrote:
>> >> I need a MySQL consultant for a small consultation (20 min.) to help
>> >> me designing a DB for ~30mil rows, whose size is about 10Gb. The
>> >> consultant must have proven experience with building using and
>> >> optimizing DBs of this scale. I only need to consult him by phone
for
>> >> 10-20 minutes, consultation by email is also possible. I need it
>> >> pretty soon.
>> >>
>> >> Regards
>> >> Elazar Leibovich
>> >> 054.4 9 6 7 7.8.4
>> >>
>> >
>> > =================================================================
>> > To unsubscribe, send mail to [EMAIL PROTECTED] with
>> > the word "unsubscribe" in the message body, e.g., run the command
>> > echo unsubscribe | mail [EMAIL PROTECTED]
>> >
>> >
>>
>> --
>> EE 77 7F 30 4A 64 2E C5 83 5F E7 49 A6 82 29 BA ~. .~ Tk Open
>> Systems
>>
=}------------------------------------------------ooO--U--Ooo------------{=
>> - [EMAIL PROTECTED] - tel: +972.2.679.5364, http://www.tkos.co.il -
>>
>
--
EE 77 7F 30 4A 64 2E C5 83 5F E7 49 A6 82 29 BA ~. .~ Tk Open
Systems
=}------------------------------------------------ooO--U--Ooo------------{=
- [EMAIL PROTECTED] - tel: +972.2.679.5364, http://www.tkos.co.il -
--
EE 77 7F 30 4A 64 2E C5 83 5F E7 49 A6 82 29 BA ~. .~ Tk Open Systems
=}------------------------------------------------ooO--U--Ooo------------{=
- [EMAIL PROTECTED] - tel: +972.2.679.5364, http://www.tkos.co.il -
=================================================================
To unsubscribe, send mail to [EMAIL PROTECTED] with
the word "unsubscribe" in the message body, e.g., run the command
echo unsubscribe | mail [EMAIL PROTECTED]
