views:

163

answers:

3

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?

+13  A: 

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:

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.
hobodave
+2  A: 

Procedure Analyse

select * from yourtable procedure analyse()\G

The above will let others know the max and min values stored in the table. That helps.

shantanuo
A: 

Knowing which indexes you have on the tables concerned is vital, imo. You state you are using a substring of column_bar in the where clause - you may need to denormalize and store this substring in another column and then index it. There again cardinality of the column can make it worthless using an index on that column, if (for example) there are only 2 distinct values present. For a useful video tutorial on Performance Tuning Best Practices watch this youtube video by Jay Pipes.

DBMarcos99
I'm not asking for help. The examples in my question were just that, examples. Indices are shown by SHOW CREATE TABLE as well.
hobodave
You asked in your ultimate sentence (and this inferred what you were asking) "What helps you?" I fail to see how my answer falls short of this - the stuff I entered has certainly helped me in terms of optimizing and fixing queries in Mysql. Still, your question, your rules, your karma.
DBMarcos99
Oh I see - you thought I'd done an in-depth analysis of your code (would have been far more verbose if I had). Perhaps I could have phrased it better, but the link to the video tutorial should have been indicated better. Understand your comment (I think) now..
DBMarcos99
"You state you are using a substring of column_bar in the where clause - you may need to denormalize and store this substring in another column and then index it." - your phrasing could use a lot of work. That seems to me like you took that fanciful query and were attempting to analyze it and suggest how I could improve it.
hobodave
I removed my downvote, since I mistook you. Your answer is still cryptic and confusing imo.
hobodave
Fair enough, I admit I could have phrased it better. Probably why I didn't end up becoming a document writer :(
DBMarcos99