Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>hope you find this helpful - <a href="http://pastie.org/1105206" rel="nofollow noreferrer">http://pastie.org/1105206</a></p> <pre><code>drop table if exists poster; create table poster ( poster_id int unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists category; create table category ( cat_id mediumint unsigned not null auto_increment primary key, name varchar(255) not null unique ) engine = innodb; drop table if exists poster_category; create table poster_category ( cat_id mediumint unsigned not null, poster_id int unsigned not null, primary key (cat_id, poster_id) -- note the clustered composite index !! ) engine = innodb; -- FYI http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html select count(*) from category count(*) ======== 500,000 select count(*) from poster count(*) ======== 1,000,000 select count(*) from poster_category count(*) ======== 125,675,688 select count(*) from poster_category where cat_id = 623 count(*) ======== 342,820 explain select p.*, c.* from poster_category pc inner join category c on pc.cat_id = c.cat_id inner join poster p on pc.poster_id = p.poster_id where pc.cat_id = 623 order by p.name limit 32; id select_type table type possible_keys key key_len ref rows == =========== ===== ==== ============= === ======= === ==== 1 SIMPLE c const PRIMARY PRIMARY 3 const 1 1 SIMPLE p index PRIMARY name 257 null 32 1 SIMPLE pc eq_ref PRIMARY PRIMARY 7 const,foo_db.p.poster_id 1 select p.*, c.* from poster_category pc inner join category c on pc.cat_id = c.cat_id inner join poster p on pc.poster_id = p.poster_id where pc.cat_id = 623 order by p.name limit 32; Statement:21/08/2010 0:00:00.021: Query OK </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PORewriting mysql select to reduce time and writing tmp to disk
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJon Black
    1. USJon Black
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PORewriting mysql select to reduce time and writing tmp to disk
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan I ask why you chose innodb? (I'm not really up on the differences.)
      singulars
      1. PO
      1. USIan
    2. COdid you check the explain plan and the resultant query speed ? clustered indexes in a word.
      singulars
      1. PO
      1. USJon Black
    3. COyou might want to take a look at this too http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
      singulars
      1. PO
      1. USJon Black
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload