Здравейте
значи имам тези 4 таблици:
Messages
+----------+-----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------------------+-------+
| ID | mediumint(8) unsigned | | PRI | 0 | |
| Body | text | | | | |
| Header | varchar(150) | | | | |
| UserID | smallint(5) unsigned | | MUL | 0 | |
| Board | smallint(5) unsigned | | MUL | 0 | |
| DatePost | datetime | | | 0000-00-00 00:00:00 | |
+----------+-----------------------+------+-----+---------------------+-------+
users
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | | UNI | 0 | |
| user | char(25) | | PRI | | |
| pass | char(20) | | PRI | | |
+--------+----------------------+------+-----+---------+-------+
Statistic
+----------+-----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------------------+-------+
| ID | mediumint(8) unsigned | | PRI | 0 | |
| Seen | smallint(5) unsigned | | | 0 | |
| Replyes | tinyint(3) unsigned | | | 0 | |
| LastPost | datetime | | | 0000-00-00 00:00:00 | |
+----------+-----------------------+------+-----+---------------------+-------+
Boards
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| BID | smallint(5) unsigned | | PRI | 0 | |
| Name | char(80) | | | | |
+--------+----------------------+------+-----+---------+-------+
Когато изпълня
explain select Messages.ID, Body, Header, users.user as User, Boards.name, DatePost, Seen, Replyes, LastPost from Messages, Statistic, login.users, Boards where (Messages.ID = 1 and Statistic.ID = 1 and users.id = Messages.UserID and Boards.BID = Messages.Board) or (Messages.ID = 2 and Statistic.ID = 2 and users.id = Messages.UserID and Boards.BID = Messages.Board);
резултатът е следния:
+-----------+--------+------------------+---------+---------+-----------------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+--------+------------------+---------+---------+-----------------+------+-------------------------+
| Boards | ALL | PRIMARY | NULL | NULL | NULL | 2 | |
| Messages | ref | PRIMARY,user,brd | brd | 2 | Boards.BID | 1 | where used |
| users | eq_ref | id | id | 2 | Messages.UserID | 1 | where used; Using index |
| Statistic | range | PRIMARY | PRIMARY | 3 | NULL | 2 | where used |
+-----------+--------+------------------+---------+---------+-----------------+------+-------------------------+
когато оставя само едното условие резултатът е доста по-добър:
mysql> explain select Messages.ID, Body, Header, login.users.user as User, Boards.name, DatePost, Seen, Replyes, LastPost from Messages, Statistic, login.users, Boards where (Messages.ID = 1 and Statistic.ID = 1 and login.users.id = Messages.UserID and Boards.BID = Messages.Board);
+-----------+-------+------------------+---------+---------+-------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+-------+------------------+---------+---------+-------+------+-------+
| Messages | const | PRIMARY,user,brd | PRIMARY | 3 | const | 1 | |
| Statistic | const | PRIMARY | PRIMARY | 3 | const | 1 | |
| users | const | id | id | 2 | const | 1 | |
| Boards | const | PRIMARY | PRIMARY | 2 | const | 1 | |
+-----------+-------+------------------+---------+---------+-------+------+-------+
4 rows in set (0.03 sec)
Може ли някой да ми обясни защо когато имам двете части на условието таблицата Board се сканира цялата (има само 2 реда в нея) въпреки че връзката с нея става чрез primary key. Къде съм сбъркал при по-горната заявка и как е по-добре да я структурирам за да не се получава и за в бъдеще подобна ситуация. Благодаря предварително
:-)
Защо става това?
