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.