StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h2>Use SHOW CREATE TABLE</h2> <hr> <p>This tells me more about your tables than your words ever could:</p> <pre><code>mysql> show create table magic\G *************************** 1. row *************************** Table: magic Create Table: CREATE TABLE `magic` ( `id` int(11) DEFAULT NULL, `what` varchar(255) DEFAULT NULL, `the` datetime DEFAULT NULL, `heck` text, `soup_is_good` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) </code></pre> <p><strong>CAVEAT</strong>: If you have 70 columns in your table, <em>omit the unnecessary ones</em>. What's necessary?</p> <ul> <li>Fields JOINed on</li> <li>Fields SELECTed</li> <li>Fields WHEREed on</li> </ul> <h2>Use EXPLAIN</h2> <hr> <p>This allows me to see how best to optimize your currently working, yet presumably slow query:</p> <pre><code>mysql> explain select * from magic\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: magic type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: 1 row in set (0.00 sec) </code></pre> <h2>Use \G</h2> <hr> <p>Having to scroll right is <em>generally</em> an inconvenience.</p> <p><strong>Usual:</strong></p> <pre><code>mysql> select * from magic; +------------+-------------------------------+---------------------+-------------------+--------------+ | id | what | the | heck | soup_is_good | +------------+-------------------------------+---------------------+-------------------+--------------+ | 1000000000 | A really long text string yay | 2009-07-29 22:28:17 | OOOH A TEXT FIELD | 100.5 | +------------+-------------------------------+---------------------+-------------------+--------------+ 1 row in set (0.00 sec) </code></pre> <p><strong>Better:</strong></p> <pre><code>mysql> select * from magic\G *************************** 1. row *************************** id: 1000000000 what: A really long text string yay the: 2009-07-29 22:28:17 heck: OOOH A TEXT FIELD soup_is_good: 100.5 1 row in set (0.00 sec) </code></pre> <p><strong>CAVEAT:</strong> \G obviously turns one row of data into several. This becomes equally cumbersome for several rows of data. Do what looks best. </p> <p>Use an external pastebin for obnoxiously large chunks of data:</p> <ul> <li><a href="http://pastie.org/" rel="nofollow noreferrer">Pastie</a></li> <li><a href="http://gist.github.com/" rel="nofollow noreferrer">gist.github</a></li> </ul> <h2>Let us know your expectations</h2> <hr> <ul> <li><strong>Slow?</strong> - We don't know what slow is to you. Seconds, minutes, hours? It helps to know.</li> <li><strong>Faster</strong> - We don't know this either. What's your expectation of fast?</li> <li><strong>Frequency</strong> - Is this a query that you plan to run just once? Daily? Hundreds or thousands of times a day? This helps us know when it's <em>Good Enough</em>.</li> </ul>

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