SPLETNA SKUPNOST SPLETNI DNEVNIKI, WIKI in RSS

1) I've got "Impossible WHERE noticed after reading const tables" message, when I ran explain in MySQL. In Oracle10gR2 it was OK.

 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
SIMPLE	b	all	PRIMARY	<NULL>	<NULL>	<NULL>	3
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 ); )

select_type	table	type	possible_keys	key	key_len	ref	rows	extra
SIMPLE	a	all	<NULL>	<NULL>	<NULL>	<NULL>	4
SIMPLE	b	all	PRIMARY	<NULL>	<NULL>	<NULL>	3	Using where

 Using too many indexes on tables can make things worse. In many cases, MySQL can calculate the best possible query plan. In very large database partitioning help. MySQL partitioning