How do I ask for help optimizing & fixing queries in MySQL?

Question

MySQL questions are some of my favorites on StackOverflow.

Unfortunately, things like this:

SELECT foo, bar, baz, quux, frozzle, lambchops FROM something JOIN somethingelse ON 1=1 JOIN (SELECT * FROM areyouserious) v ON 0=5 WHERE lambchops = 'good';

make my eyes bleed.

Also, attempts at describing your schema often go like this:

I have a table CrazyTable with a column that is a date and it has a primary key of Foo_Key but I want to join on SOMETABLE using a substring of column_bar (which is in CrazyTable) which pertains to the phase of the moon (which I store in moon_phases as a thrice-serialized PHP array).

Here is an example of a question I asked, that had I not followed the steps below, I would never have gotten a satisfactory answer from anyone: I have no shame..

I will answer below with what helps me the most with getting the best answer to your question. What helps you?

Answer 1

Use SHOW CREATE TABLE

---

This tells me more about your tables than your words ever could:

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)

CAVEAT: If you have 70 columns in your table, omit the unnecessary ones. What's necessary?

• Fields JOINed on
• Fields SELECTed
• Fields WHEREed on

Use EXPLAIN

---

This allows me to see how best to optimize your currently working, yet presumably slow query:

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)

Use \G

---

Having to scroll right is generally an inconvenience.

Usual:

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)

Better:

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)

CAVEAT: \G obviously turns one row of data into several. This becomes equally cumbersome for several rows of data. Do what looks best.

Use an external pastebin for obnoxiously large chunks of data:

• Pastie
• gist.github

Let us know your expectations

---

• Slow? - We don't know what slow is to you. Seconds, minutes, hours? It helps to know.
• Faster - We don't know this either. What's your expectation of fast?
• Frequency - 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 Good Enough.