BTW - I had jumped in on the thread to explain how indexes can help
you deal with such horrible queries. Users can't always wait for the
developers.
I am using 4.0 final and I inspected the dbscripts for the original
poster's mysql. I then noted what was present and confirmed what was
missing.
So in this case I would prefer to see us work to fix the query
rather than just try and throw new indexes at the problem.
Absolutely - unless it is too large a task. Either way it is probably
a JIRA issue for Roller.
Maybe in the switch from Hibernate to JPA some setting was missed?
Maybe the 4.0 object model is more complicated that the poster's
prior 1.1?
Perhaps Dave can explain the reasons why the query is so complex. I
suspect it has to with creating a Velocity model in a single query?
Regards,
Dave
On Mar 13, 2008, at 11:07 AM, Allen Gilliland wrote:
Yes, I am convinced that sql statement is an abomination.
You said this is with 4.0 final running with the JPA backend correct?
Adding the indexes is an option, but I think the bigger problem is
that query is ridiculous. It is obviously trying to load way more
data than it should need to in a single query because a 9 way join
in 1 query is insane. I'm wondering if this is a configuration
problem with OpenJPA not properly doing lazy fetching. I would
think that at the very most you should see 3 tables involved in a
single query.
The reason I questioned the need for indexes on the 2 columns below
is that they are really only associations that should be fetched
lazily, specifically for reasons like this. There is no need for
the object model to fetch and populate those category objects when
you are querying for a list of weblog entries.
So in this case I would prefer to see us work to fix the query
rather than just try and throw new indexes at the problem.
-- Allen
David Fisher wrote:
From my experience the need for an index on a database table is
more a function of the database configuration and the current size
of a table. Many times the need has cropped up in my own webapp
development. One of the reasons why we rolled our own set of
classes is that it is easy to get to and manipulate the sql.
Tuning is a fine art, sometimes an index is indicated and other
times a rewrite of the statement with a hint so that it is joined
in another manner. A third option is found with Oracle - analyze
the tables - this allows the Database to decide how best to
organize the table's index and that can improve plans. If MySQL
offers a similar feature perhaps this will avoid the index.
Let's look at the way the SQL is constructed:
FROM roller_comment t0 INNER JOIN weblogentry t1 ON t0.entryid =
t1.id LEFT OUTER JOIN weblogcategory t2 ON t1.categoryid = t2.id
LEFT OUTER JOIN
rolleruser t8 ON t1.userid = t8.id LEFT OUTER JOIN website t9 ON
t1.websiteid =
t9.id LEFT OUTER JOIN weblogcategory t3 ON t2.parentid = t3.id
LEFT OUTER JOIN
website t4 ON t2.websiteid = t4.id LEFT OUTER JOIN weblogcategory
t5 ON
t4.bloggercatid = t5.id LEFT OUTER JOIN rolleruser t6 ON t4.userid
= t6.id LEFT
OUTER JOIN weblogcategory t7 ON t4.defaultcatid = t7.id WHERE
(t1.websiteid =
'8a926693072c38bf010741c83fcb36d6' AND t0.status = 'APPROVED')
ORDER BY
t0.posttime DESC LIMIT 0, 10
This rewrites as:
FROM roller_comment t0, weblogentry t1, weblogcategory t2,
rolleruser t8, website t9, weblogcategory t3, website t4,
weblogcategory t5, rolleruser t6, weblogcategory t7
WHERE t1.categoryid = t2.id AND t1.userid = t8.id AND t1.websiteid
= t9.id AND t2.parentid = t3.id AND t2.websiteid = t4.id AND
t4.bloggercatid = t5.id AND t4.userid = t6.id AND t4.defaultcatid
= t7.id AND
t1.websiteid = '8a926693072c38bf010741c83fcb36d6' AND t0.status =
'APPROVED' ORDER BY
t0.posttime DESC LIMIT 0, 10
These indexes are provided:
website.userid
weblogentry.categoryid
weblogentry.userid
weblogentry.websiteid
weblogcategory.id (primary key)
weblogcategory.parentid
weblogcategory.websiteid
rolleruser.id (primary key)
roller_comment.status
create index ws_bloggercatid_idx on website(bloggercatid);
is for "t4.bloggercatid = t5.id"
create index ws_defaultcatid_idx on website(defaultcatid );
is for "t4.defaultcatid = t7.id"
In other words every other possible index that would help make
this horrible join function except for the two missed:
My company has a blog service with 30k blogs and 70k unique
browsers per week.
This clearly indicates that without the two indexes the 30K blogs
impacts every JOIN significantly! The factor is roughly by 30,000
x 30,000 or 900,000,000. Maybe not that bad, but you can see the
EXPLOSION!
Are you convinced?
Regards,
Dave
On Mar 12, 2008, at 11:36 AM, Allen Gilliland wrote:
Yes, I understand why we use indexes, what I meant was that when
you are suggesting that we need a new index that you provide some
evidence about why that particular index is necessary.
Your example below is completely valid, but the ur_userid_idx
index has always been there, so that's not a new index.
For example, you said we need to add these 2 indexes ...
create index ws_bloggercatid_idx on website(bloggercatid);
create index ws_defaultcatid_idx on website(defaultcatid );
why? off the top of my head I can't think of a reason those
indexes would be of benefit because I don't believe we run any
standard queries which do a lookup or join on those columns. I
may be wrong, but that's why I would like some evidence before we
would just add the indexes for no reason.
-- Allen
Boris Milikič wrote:
1) I've got "Impossible WHERE noticed after reading const
tables" message, when I ran explain on sql from prevoious e-mail:
SELECT t0.id, t0.content, t0.contenttype, t0.email, t0.name,
t0.notify, t0.plugins, t0.posttime, ....
2) So I run explain on this simple query:
explain select a.rolename ,b.username from rolleruser b,
userrole a where b.id=a.userid;
3) Explanations of why indexes are necessary
Column "type" in following table is the join type. Type ref
means, that all rows with matching index values are read from
this table for each combination of rows from the previous
tables, example a).
For a tables that are completely read in sequence from the hard
drive EXPLAIN lists "ALL" in the "type" column. To the second
table in the join plan for a two table query, EXPLAIN lists
type: ALL, as for table without index, example b). In example
b) when EXPLAIN lists type: ALL for each table in a join "this
output indicates that MySQL is generating a Cartesian product of
all the tables; that is, every combination of rows" (MySQL
manual). In simpler terms: Two tables of 10 rows each joined
together does not result in 20 rows, it results in 100 rows (10
multiplied by 10).
a) with index
select_type table type possible_keys key
key_len ref rows
extra 1 SIMPLE b
ALL PRIMARY <NULL> <NULL> <NULL> 3 1
SIMPLE a ref ur_userid_idx ur_userid_idx 144
roller.b.id 1 b) without index (drop index ur_userid_idx on
userrole( userid );) 1
SIMPLE a ALL <NULL> <NULL> <NULL> <NULL>
4 1 SIMPLE b ALL PRIMARY <NULL> <NULL>
<NULL> 3 Using where
Using too many indexes on tables can make things worse, as you
said. In many cases, MySQL can calculate the best possible query
plan. In very large database partitioning help.
-- Boris
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, March 07, 2008 6:04 PM
To: [email protected]
Subject: Re: Problem with database load
Can you also provide explanations of why you think those indexes
are necessary? Having too many indexes on tables can actually
make things worse, so you don't want to just flood the db with
tons of indexes.
-- Allen
Boris Milikič wrote:
I just found one more missing:
create index ws_posttime_idx on roller_comment (posttime);
Next weekend I will walk through sql log and dbcreate.sql
script and record in JIRA if I find something.
Boris
-----Original Message-----
From: Dave [mailto:[EMAIL PROTECTED]
Sent: Friday, March 07, 2008 4:31 PM
To: [email protected]
Subject: Re: Problem with database load
On Fri, Mar 7, 2008 at 10:15 AM, Boris Milikič
<[EMAIL PROTECTED]> wrote:
Did you created missing indexes in rollerdb (4.0):
create index ws_bloggercatid_idx on website(bloggercatid);
create index ws_defaultcatid_idx on website(defaultcatid );
Thanks Boris,
I just opened an issue for this:
https://issues.apache.org/roller/browse/ROL-1687
Are there any other 4.0 database issues that you know of that
are not on record in JIRA?
- Dave
