EXPLAIN and VERTICAL PARTITION

[birbal]

what is the practical use of "explain" and VERTICAL PARTITION? and how?i was reading it in mysql official site. though its purpose is not clear to me. need some info about it. provide some link rather than mysql.com for info or please illaborate it here.thanks!

[justsomeguy]

Using EXPLAIN with a SELECT query prints some information about the various tables involved in the query, how many rows each table has, the order they get joined in, etc. The practical use is for DBAs to analyze their more expensive queries to show where the bottlenecks might be. This query may be fairly difficult to understand in terms of the tables that it joins, how many records are in each table and how many are in the joined tables, etc:

id  select_type   table             type    possible_keys   key       key_len   ref                 rows  Extra1   PRIMARY       <derived5>        ALL     NULL            NULL      NULL      NULL                27    Using where; Using temporary; Using filesort1   PRIMARY       t                 eq_ref  PRIMARY         PRIMARY   4         r3.ticket           11   PRIMARY       u                 eq_ref  PRIMARY         PRIMARY   4         tickets.t.user      11   PRIMARY       a                 eq_ref  PRIMARY         PRIMARY   4         tickets.u.account   11   PRIMARY       l                 eq_ref  PRIMARY         PRIMARY   4         tickets.u.location  11   PRIMARY       <derived2>        ALL     NULL            NULL      NULL      NULL                271   PRIMARY       <derived3>        ALL     NULL            NULL      NULL      NULL                271   PRIMARY       <derived4>        ALL     NULL            NULL      NULL      NULL                45   DERIVED       ticket_responses  ALL     NULL            NULL      NULL      NULL                54    Using temporary; Using filesort4   DERIVED       billing_items     ALL     NULL            NULL      NULL      NULL                8     Using temporary; Using filesort3   DERIVED       ticket_responses  ALL     NULL            NULL      NULL      NULL                54    Using temporary; Using filesort2   DERIVED       ticket_responses  ALL     NULL            NULL      NULL      NULL                54    Using temporary; Using filesort

Vertical partitioning is the practice of splitting up table fields into separate tables based on the frequency they get accessed. The most-frequently accessed fields would go in one table and larger less-frequently accessed fields go in another one.

[birbal]

is there any relation beetwin optimising database and EXPLAIN. is it make fast db anyhow?so EXPLAIN is used to check the joined tables.I can use that information to move the order of the joins to help keep the joined tables as small as possible.i am not getting this. what does it mean actually?

[justsomeguy]

is there any relation beetwin optimising database and EXPLAIN.

I guess so. A database designer would use the output from EXPLAIN to help figure out how to optimize the database (or at least that particular query).

i am not getting this. what does it mean actually?

SQL optimization is a very large topic, there are a lot of books on the subject. Optimizing queries can be both a science and an art.