roller-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boris Milikič <boris.mili...@mf-rs.si>
Subject RE: Problem with database load
Date Mon, 10 Mar 2008 16:02:57 GMT

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: Allen.Gilliland@Sun.COM [mailto:Allen.Gilliland@Sun.COM] 
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č 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:snoopdave@gmail.com]
> Sent: Friday, March 07, 2008 4:31 PM
> To: user@roller.apache.org
> Subject: Re: Problem with database load
> 
> On Fri, Mar 7, 2008 at 10:15 AM, Boris Milikič <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 );
> 
> 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

Mime
View raw message