StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would suggest modifying the storage engine and schema to utilize foreign keys.</p> <pre><code>CREATE TABLE `article` ( `id` int(11) NOT NULL auto_increment, `title` varchar(60) NOT NULL, `time_stamp` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000001 ; CREATE TABLE `tag` ( `id` int(11) NOT NULL auto_increment, `tag` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `article_tag` ( `id` int(11) NOT NULL auto_increment, `article_id` int(11) NOT NULL, `tag_id` int(11) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`article_id`) REFERENCES article(id), FOREIGN KEY (`tag_id`) REFERENCES tag(id) ) ENGINE=Innodb; </code></pre> <p>Which results in a query like so:</p> <pre><code>EXPLAIN SELECT * FROM article JOIN article_tag ON article.id = article_tag.id JOIN tag ON article_tag.tag_id = tag.id WHERE tag.tag="Acer"; +----+-------------+-------------+--------+----------------+---------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+----------------+---------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | article_tag | ALL | PRIMARY,tag_id | NULL | NULL | NULL | 1 | | | 1 | SIMPLE | tag | eq_ref | PRIMARY | PRIMARY | 4 | temp.article_tag.tag_id | 1 | Using where | | 1 | SIMPLE | article | eq_ref | PRIMARY | PRIMARY | 4 | temp.article_tag.id | 1 | | +----+-------------+-------------+--------+----------------+---------+---------+-------------------------+------+-------------+ 3 rows in set (0.00 sec) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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