Mailing-List: contact user-help@roller.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@roller.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Problem with database load
Date: Mon, 10 Mar 2008 17:02:57 +0100
Message-ID: <401175E741D5334E8D846560862B5B6F02BEECDB@mfrswdneecs01.mf.si>
Thread-Topic: Problem with database load
Thread-Index: AciAdS4VSlqCIPU/TYuA6Sz24cnwxACSrkSA
References: <20080307104107.167128738F@cal1-1.us4.outblaze.com>
 <401175E741D5334E8D846560862B5B6F02BEEA25@mfrswdneecs01.mf.si>
 <8fb9ac720803070730i512d6afemac2d24134d69a016@mail.gmail.com>
 <401175E741D5334E8D846560862B5B6F02BEEA2E@mfrswdneecs01.mf.si>
 <47D17573.4070800@Sun.COM>
From: =?iso-8859-2?Q?Boris_Miliki=E8?= <boris.milikic@mf-rs.si>
To: <user@roller.apache.org>


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=20
where b.id=3Da.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).=20

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=20
								=09
1	SIMPLE	b	ALL	PRIMARY	<NULL>	<NULL>	<NULL>	3=09

1	SIMPLE	a	ref	ur_userid_idx	ur_userid_idx	144	roller.b.id	1=09

b) without index (drop index ur_userid_idx on userrole( userid );) =09
							=09
1	SIMPLE	a	ALL	<NULL>	<NULL>	<NULL>	<NULL>	4=09

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: Allen.Gilliland@Sun.COM [mailto:Allen.Gilliland@Sun.COM]=20
Sent: Friday, March 07, 2008 6:04 PM
To: user@roller.apache.org
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=E8 wrote:
> I just found  one more missing:
>=20
> create index ws_posttime_idx    on roller_comment (posttime);
>=20
> Next weekend I will walk through sql log and  dbcreate.sql script and =
record in JIRA if I find something.
>=20
> Boris
>=20
> -----Original Message-----
> From: Dave [mailto:snoopdave@gmail.com]
> Sent: Friday, March 07, 2008 4:31 PM
> To: user@roller.apache.org
> Subject: Re: Problem with database load
>=20
> On Fri, Mar 7, 2008 at 10:15 AM, Boris Miliki=E8 =
<boris.milikic@mf-rs.si> 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 );
>=20
> Thanks Boris,
>=20
> I just opened an issue for this:
> https://issues.apache.org/roller/browse/ROL-1687
>=20
> Are there any other 4.0 database issues that you know of that are not =
on record in JIRA?
>=20
> - Dave