Slow DB

[frizzle]

Hi there

I have a news publishing site. Newsitems (of course) are ordered by
date.
Each record has:
id INT(11), publish_date DATETIME() and an url VARCHAR(50) (unique)
(and more fields, but not of importance now)

I loaded up the database with fake random data. So the dates were also
inserted randomly.
(Anywhere between 2000 and 2010 )
1.000.000 + records gave me extremely fast result being called from the
url, but now
with only 1.00.000 records left, it takes 7 seconds+ to get 5 records,
ordered by date.

How can i optimize such a system? I tried EXPLAIN, but that didn't make
sense to me ... :(

Frizzle.

[Jerry Stuckle]

frizzle wrote:

> Hi there
>
> I have a news publishing site. Newsitems (of course) are ordered by
> date.
> Each record has:
> id INT(11), publish_date DATETIME() and an url VARCHAR(50) (unique)
> (and more fields, but not of importance now)
>
> I loaded up the database with fake random data. So the dates were also
> inserted randomly.
> (Anywhere between 2000 and 2010 )
> 1.000.000 + records gave me extremely fast result being called from the
> url, but now
> with only 1.00.000 records left, it takes 7 seconds+ to get 5 records,
> ordered by date.
>
> How can i optimize such a system? I tried EXPLAIN, but that didn't make
> sense to me ... :(
>
> Frizzle.
>

EXPLAIN is going to be your best resource. You should read up on it in
the MySQL manual.

However, don't figure the same results with random data that you get
with your real data. Data distribution will affect response and EXPLAIN
output.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Dikkie Dik]

Is there an index on the datetime field?

The unique constraint of the url uses an index. Even of you did not
explicitly put an index on it, MySQL has done that itself. For the date,
MySQL probably has to use a filesort, which is slow.

Use the EXPLIAN command. If it says: using index, then it is probably
very fast already. If it says: using filesort, add the right index.

You normally add only the indexes you need. So if you would never search
on a date field, (for a log that is there only for emergency, for
example) you would not put an index on that. Not putting an index on a
field is somewhat faster when inserting rows, but by far slower when you
do a search.

Jerry Stuckle wrote:

> frizzle wrote:
>

>> Hi there
>>
>> I have a news publishing site. Newsitems (of course) are ordered by
>> date.
>> Each record has:
>> id INT(11), publish_date DATETIME() and an url VARCHAR(50) (unique)
>> (and more fields, but not of importance now)
>>
>> I loaded up the database with fake random data. So the dates were also
>> inserted randomly.
>> (Anywhere between 2000 and 2010 )
>> 1.000.000 + records gave me extremely fast result being called from the
>> url, but now
>> with only 1.00.000 records left, it takes 7 seconds+ to get 5 records,
>> ordered by date.
>>
>> How can i optimize such a system? I tried EXPLAIN, but that didn't make
>> sense to me ... :(
>>
>> Frizzle.
>>

>
> EXPLAIN is going to be your best resource. You should read up on it in
> the MySQL manual.
>
> However, don't figure the same results with random data that you get
> with your real data. Data distribution will affect response and EXPLAIN
> output.
>

[frizzle]

Dikkie Dik wrote:

> Is there an index on the datetime field?
>
> The unique constraint of the url uses an index. Even of you did not
> explicitly put an index on it, MySQL has done that itself. For the date,
> MySQL probably has to use a filesort, which is slow.
>
> Use the EXPLIAN command. If it says: using index, then it is probably
> very fast already. If it says: using filesort, add the right index.
>
> You normally add only the indexes you need. So if you would never search
> on a date field, (for a log that is there only for emergency, for
> example) you would not put an index on that. Not putting an index on a
> field is somewhat faster when inserting rows, but by far slower when you
> do a search.
>
> Jerry Stuckle wrote:

> > frizzle wrote:
> >

> >> Hi there
> >>
> >> I have a news publishing site. Newsitems (of course) are ordered by
> >> date.> >> (Anywhere between 2000 and 2010 )
> >> Each record has:
> >> id INT(11), publish_date DATETIME() and an url VARCHAR(50) (unique)
> >> (and more fields, but not of importance now)
> >>
> >> I loaded up the database with fake random data. So the dates were also
> >> inserted randomly.

> >> 1.000.000 + records gave me extremely fast result being called from the
> >> url, but now
> >> with only 1.00.000 records left, it takes 7 seconds+ to get 5 records,
> >> ordered by date.
> >>
> >> How can i optimize such a system? I tried EXPLAIN, but that didn't make
> >> sense to me ... :(
> >>
> >> Frizzle.
> >>

> >
> > EXPLAIN is going to be your best resource. You should read up on it in
> > the MySQL manual.
> >
> > However, don't figure the same results with random data that you get
> > with your real data. Data distribution will affect response and EXPLAIN
> > output.
> >

Dear Dikkie Dik (asjemenou),

it did return something with filesort, and i don't have any clue where
to find my solution. The EXPLAIN did'nt get me a lot wiser either ...
:(

Frizzle.

[Dikkie Dik]

Does this help?
[url]http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html[/url]

> it did return something with filesort, and i don't have any clue where
> to find my solution. The EXPLAIN did'nt get me a lot wiser either ...
> :(

Kopje,
Dikkie

[frizzle]

Dikkie Dik wrote:

> Does this help?
> [url]http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html[/url]
>

> > it did return something with filesort, and i don't have any clue where
> > to find my solution. The EXPLAIN did'nt get me a lot wiser either ...
> > :(

>
>
> Kopje,
> Dikkie

I read the document, and i couldn't figure anything that i'm doing
wrong :(
(Could it matter i set the index *after* inserting the rows?)

The exact query is the following:

SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
COUNT(f.`id`) AS 'fulltext',
COUNT(c.`id`) AS 'comments'
FROM `ne_topics` n
LEFT JOIN `ne_fulltext` f
ON f.`id` = n.`id`
LEFT JOIN `ne_comments` c
ON c.`topic_id` = n.`id`
GROUP BY n.`id`
ORDER BY n.`published` DESC
LIMIT 5


EXPLAIN returns:

table type possible_keys key key_len ref rows
Extra
n ALL NULL NULL NULL NULL 100000 Using
temporary; Using filesort
f index id id 4 NULL 1
Using index
c ALL topic_id NULL NULL NULL 1

[frizzle]

Dikkie Dik wrote:

> Does this help?
> [url]http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html[/url]
>

> > it did return something with filesort, and i don't have any clue where
> > to find my solution. The EXPLAIN did'nt get me a lot wiser either ...
> > :(

>
>
> Kopje,
> Dikkie

I read the document, and i couldn't figure anything that i'm doing
wrong :(
(Could it matter i set the index *after* inserting the rows?)

The exact query is the following:

SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
COUNT(f.`id`) AS 'fulltext',
COUNT(c.`id`) AS 'comments'
FROM `ne_topics` n
LEFT JOIN `ne_fulltext` f
ON f.`id` = n.`id`
LEFT JOIN `ne_comments` c
ON c.`topic_id` = n.`id`
GROUP BY n.`id`
ORDER BY n.`published` DESC
LIMIT 5


EXPLAIN returns:

table type possible_keys key key_len ref rows
Extra
n ALL NULL NULL NULL NULL 100000 Using
temporary; Using filesort
f index id id 4 NULL 1
Using index
c ALL topic_id NULL NULL NULL 1



Greetings Frizzle.

[Dikkie Dik]

<snip>

> The exact query is the following:
>
> SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> COUNT(f.`id`) AS 'fulltext',
> COUNT(c.`id`) AS 'comments'
> FROM `ne_topics` n
> LEFT JOIN `ne_fulltext` f
> ON f.`id` = n.`id`
> LEFT JOIN `ne_comments` c
> ON c.`topic_id` = n.`id`
> GROUP BY n.`id`
> ORDER BY n.`published` DESC
> LIMIT 5
>
>
> EXPLAIN returns:
>
> table type possible_keys key key_len ref rows
> Extra
> n ALL NULL NULL NULL NULL 100000 Using
> temporary; Using filesort
> f index id id 4 NULL 1
> Using index
> c ALL topic_id NULL NULL NULL 1

Let me explain "explain":
The first row says that table "n" (ne_topics) has to be searched
entirely because there is no index that can be used
(possible_keys=NULL). So it has to read the entire table in memory
somehow, sort it, and return the 5 rows that come out on top of that
action. If there was an index on ne_topics.published, the top 5 could
just be taken from that index, the corresponding rows looked up and the
joins could be made. As you can see, the possible_keys for the last two
rows are not NULL, so the joins can be made quickly.

Look up the CREATE INDEX command to create an index on
ne_topics.published and see if the performance gets better.

Kopje,
Dikkie.

[frizzle]

Dikkie Dik wrote:

> <snip>

> > The exact query is the following:
> >
> > SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> > COUNT(f.`id`) AS 'fulltext',
> > COUNT(c.`id`) AS 'comments'
> > FROM `ne_topics` n
> > LEFT JOIN `ne_fulltext` f
> > ON f.`id` = n.`id`
> > LEFT JOIN `ne_comments` c
> > ON c.`topic_id` = n.`id`
> > GROUP BY n.`id`
> > ORDER BY n.`published` DESC
> > LIMIT 5
> >
> >
> > EXPLAIN returns:
> >
> > table type possible_keys key key_len ref rows
> > Extra
> > n ALL NULL NULL NULL NULL 100000 Using
> > temporary; Using filesort
> > f index id id 4 NULL 1
> > Using index
> > c ALL topic_id NULL NULL NULL 1

> Let me explain "explain":
> The first row says that table "n" (ne_topics) has to be searched
> entirely because there is no index that can be used
> (possible_keys=NULL). So it has to read the entire table in memory
> somehow, sort it, and return the 5 rows that come out on top of that
> action. If there was an index on ne_topics.published, the top 5 could
> just be taken from that index, the corresponding rows looked up and the
> joins could be made. As you can see, the possible_keys for the last two
> rows are not NULL, so the joins can be made quickly.
>
> Look up the CREATE INDEX command to create an index on
> ne_topics.published and see if the performance gets better.
>
> Kopje,
> Dikkie.

Allememachies,

It appeared the slow DB wasn't caused by the actual ne_topics table,
but tjhe joins in the query.
Which i don't get, because the two other table had a maximum of 1
record in it.
I've read upon "JOIN" etc, but wouldn't know *why* it makes it zo
incerdibly slow.
If i leave the joins out, result appears (almost) instantly ...

Frizzle.

[Daniel Schneller]

Hi!

>>>SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
>>> COUNT(f.`id`) AS 'fulltext',
>>> COUNT(c.`id`) AS 'comments'
>>>FROM `ne_topics` n
>>>LEFT JOIN `ne_fulltext` f
>>>ON f.`id` = n.`id`
>>>LEFT JOIN `ne_comments` c
>>>ON c.`topic_id` = n.`id`
>>>GROUP BY n.`id`
>>>ORDER BY n.`published` DESC
>>>LIMIT 5
>>>
>>>EXPLAIN returns:
>>>
>>>table type possible_keys key key_len ref rows
>>>Extra
>>>n ALL NULL NULL NULL NULL 100000 Using
>>>temporary; Using filesort
>>>f index id id 4 NULL 1
>>> Using index
>>>c ALL topic_id NULL NULL NULL 1

[...]

> It appeared the slow DB wasn't caused by the actual ne_topics table,
> but tjhe joins in the query.
> Which i don't get, because the two other table had a maximum of 1
> record in it.

You are doing a left join with ne_topics being left. A LEFT JOIN means
you want *all* rows from the table on the left. Then the database
matches ne_fulltext to those rows where ne_topics.id=ne_fulltext.id.
For those rows that do not have a match, the right side is set to null.
Because you do not specify any WHERE condition that would reduce the
number of rows that are returned from ne_topics, this will always result
in all ne_topics entries to be returned, no matter which indices you create.
Don't be fooled by the LIMIT 5 clause. This only applies *after*
everything has been done. It limits only the amount of data that is
transferred to the client; it does not limit the number of rows that
have to be evaluated.

I suggest you try a different approach (the following is untested):

SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
COUNT(f.`id`) AS 'fulltext',
COUNT(c.`id`) AS 'comments'
FROM ne_topics n
INNER JOIN ne_fulltext f on n.id=f.id
INNER JOIN ne_comments c on c.topic_id=n.id
WHERE n.published > {current-time minus some sensible range}
GROUP BY n.id
ORDER BY n.published DESC
LIMIT 5

Depending on how many matches are found this should already reduce the
number of rows. As you however do not tell exactly what result you need
for your site, I can just guess.
Because you limit to the first 5 rows after sorting by the publishing
date you should definitely consider using a condition on that date. I
suggested that in the statement above. You have to decide based on the
frequency of publications. Maybe an hour is enough, maybe a day.
Something that would usually result in at least 5 rows.

To make sure you get exactly 5 all the time you could use something like
SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
COUNT(f.`id`) AS 'fulltext',
COUNT(c.`id`) AS 'comments'
FROM ne_topics n
INNER JOIN ne_fulltext f on n.id=f.id
INNER JOIN ne_comments c on c.topic_id=n.id
WHERE n.id in (select id from ne_topics order by published desc limit 5)
ORDER BY n.published DESC
LIMIT 5

I have not tried this one either, but as long as you have an index on
ne_topics.published the subselect should return the 5 most recent ids
very quickly and then only do the join for those.

I leave the details to you, including the syntax errors and reading the
chapter about indices and joins more thoroughly :)

Daniel

[frizzle]

Daniel Schneller wrote:

> Hi!
>

> >>>SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> >>> COUNT(f.`id`) AS 'fulltext',
> >>> COUNT(c.`id`) AS 'comments'
> >>>FROM `ne_topics` n
> >>>LEFT JOIN `ne_fulltext` f
> >>>ON f.`id` = n.`id`
> >>>LEFT JOIN `ne_comments` c
> >>>ON c.`topic_id` = n.`id`
> >>>GROUP BY n.`id`
> >>>ORDER BY n.`published` DESC
> >>>LIMIT 5
> >>>
> >>>EXPLAIN returns:
> >>>
> >>>table type possible_keys key key_len ref rows
> >>>Extra
> >>>n ALL NULL NULL NULL NULL 100000 Using
> >>>temporary; Using filesort
> >>>f index id id 4 NULL 1
> >>> Using index
> >>>c ALL topic_id NULL NULL NULL 1

> [...]

> > It appeared the slow DB wasn't caused by the actual ne_topics table,
> > but tjhe joins in the query.
> > Which i don't get, because the two other table had a maximum of 1
> > record in it.

>
> You are doing a left join with ne_topics being left. A LEFT JOIN means
> you want *all* rows from the table on the left. Then the database
> matches ne_fulltext to those rows where ne_topics.id=ne_fulltext.id.
> For those rows that do not have a match, the right side is set to null.
> Because you do not specify any WHERE condition that would reduce the
> number of rows that are returned from ne_topics, this will always result
> in all ne_topics entries to be returned, no matter which indices you create.
> Don't be fooled by the LIMIT 5 clause. This only applies *after*
> everything has been done. It limits only the amount of data that is
> transferred to the client; it does not limit the number of rows that
> have to be evaluated.
>
> I suggest you try a different approach (the following is untested):
>
> SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> COUNT(f.`id`) AS 'fulltext',
> COUNT(c.`id`) AS 'comments'
> FROM ne_topics n
> INNER JOIN ne_fulltext f on n.id=f.id
> INNER JOIN ne_comments c on c.topic_id=n.id
> WHERE n.published > {current-time minus some sensible range}
> GROUP BY n.id
> ORDER BY n.published DESC
> LIMIT 5
>
> Depending on how many matches are found this should already reduce the
> number of rows. As you however do not tell exactly what result you need
> for your site, I can just guess.
> Because you limit to the first 5 rows after sorting by the publishing
> date you should definitely consider using a condition on that date. I
> suggested that in the statement above. You have to decide based on the
> frequency of publications. Maybe an hour is enough, maybe a day.
> Something that would usually result in at least 5 rows.
>
> To make sure you get exactly 5 all the time you could use something like
> SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> COUNT(f.`id`) AS 'fulltext',
> COUNT(c.`id`) AS 'comments'
> FROM ne_topics n
> INNER JOIN ne_fulltext f on n.id=f.id
> INNER JOIN ne_comments c on c.topic_id=n.id
> WHERE n.id in (select id from ne_topics order by published desc limit 5)
> ORDER BY n.published DESC
> LIMIT 5
>
> I have not tried this one either, but as long as you have an index on
> ne_topics.published the subselect should return the 5 most recent ids
> very quickly and then only do the join for those.
>
> I leave the details to you, including the syntax errors and reading the
> chapter about indices and joins more thoroughly :)
>
> Daniel

Well thanks Daniel for your reply.
I get the idea. I have no time now, but i understand it would be best
first to get the ID's, and then get the info & joins according to them,
so i would only have to perform (in this case) 5 JOINS.

My condition btw (if i understand what you mean there) was:
WHERE n.`published` <= NOW()

I will try this tomorrow.
Thanks a lot.

Frizzle.

[frizzle]

frizzle wrote:

> Daniel Schneller wrote:

> > Hi!
> >

> > >>>SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> > >>> COUNT(f.`id`) AS 'fulltext',
> > >>> COUNT(c.`id`) AS 'comments'
> > >>>FROM `ne_topics` n
> > >>>LEFT JOIN `ne_fulltext` f
> > >>>ON f.`id` = n.`id`
> > >>>LEFT JOIN `ne_comments` c
> > >>>ON c.`topic_id` = n.`id`
> > >>>GROUP BY n.`id`
> > >>>ORDER BY n.`published` DESC
> > >>>LIMIT 5
> > >>>
> > >>>EXPLAIN returns:
> > >>>
> > >>>table type possible_keys key key_len ref rows
> > >>>Extra
> > >>>n ALL NULL NULL NULL NULL 100000 Using
> > >>>temporary; Using filesort
> > >>>f index id id 4 NULL 1
> > >>> Using index
> > >>>c ALL topic_id NULL NULL NULL 1

> > [...]

> > > It appeared the slow DB wasn't caused by the actual ne_topics table,
> > > but tjhe joins in the query.
> > > Which i don't get, because the two other table had a maximum of 1
> > > record in it.

> >
> > You are doing a left join with ne_topics being left. A LEFT JOIN means
> > you want *all* rows from the table on the left. Then the database
> > matches ne_fulltext to those rows where ne_topics.id=ne_fulltext.id.
> > For those rows that do not have a match, the right side is set to null.
> > Because you do not specify any WHERE condition that would reduce the
> > number of rows that are returned from ne_topics, this will always result
> > in all ne_topics entries to be returned, no matter which indices you create.
> > Don't be fooled by the LIMIT 5 clause. This only applies *after*
> > everything has been done. It limits only the amount of data that is
> > transferred to the client; it does not limit the number of rows that
> > have to be evaluated.
> >
> > I suggest you try a different approach (the following is untested):
> >
> > SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> > COUNT(f.`id`) AS 'fulltext',
> > COUNT(c.`id`) AS 'comments'
> > FROM ne_topics n
> > INNER JOIN ne_fulltext f on n.id=f.id
> > INNER JOIN ne_comments c on c.topic_id=n.id
> > WHERE n.published > {current-time minus some sensible range}
> > GROUP BY n.id
> > ORDER BY n.published DESC
> > LIMIT 5
> >
> > Depending on how many matches are found this should already reduce the
> > number of rows. As you however do not tell exactly what result you need
> > for your site, I can just guess.
> > Because you limit to the first 5 rows after sorting by the publishing
> > date you should definitely consider using a condition on that date. I
> > suggested that in the statement above. You have to decide based on the
> > frequency of publications. Maybe an hour is enough, maybe a day.
> > Something that would usually result in at least 5 rows.
> >
> > To make sure you get exactly 5 all the time you could use something like
> > SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`text`,
> > COUNT(f.`id`) AS 'fulltext',
> > COUNT(c.`id`) AS 'comments'
> > FROM ne_topics n
> > INNER JOIN ne_fulltext f on n.id=f.id
> > INNER JOIN ne_comments c on c.topic_id=n.id
> > WHERE n.id in (select id from ne_topics order by published desc limit 5)
> > ORDER BY n.published DESC
> > LIMIT 5
> >
> > I have not tried this one either, but as long as you have an index on
> > ne_topics.published the subselect should return the 5 most recent ids
> > very quickly and then only do the join for those.
> >
> > I leave the details to you, including the syntax errors and reading the
> > chapter about indices and joins more thoroughly :)
> >
> > Daniel

>
> Well thanks Daniel for your reply.
> I get the idea. I have no time now, but i understand it would be best
> first to get the ID's, and then get the info & joins according to them,
> so i would only have to perform (in this case) 5 JOINS.
>
> My condition btw (if i understand what you mean there) was:
> WHERE n.`published` <= NOW()
>
> I will try this tomorrow.
> Thanks a lot.
>
> Frizzle.

Even without any PHP parsing / html, only with 1 Join (should
eventually become three, beacause it should also get the user's name
.... ), it takes me more then half a second, to load, and too often a
whole second (or more). Would it be wise to keep the dates in a
separate table, or maybe just the bodytexts of the messages in another
table?

Because this really takes too long ... :( :(

Frizzle.