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
