Miscellany

01.
# mysqladmin proc

 02.
+-----+--------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

 03.
| Id  | User   | Host      | db           | Command | Time | State                | Info                                                                                                 |

 04.
+-----+--------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

 05.
| 762 | revgrp | localhost | revgrp_admin | Query   | 10   | Copying to tmp table | SELECT * FROM invoices i, customers c where i.customers_id = c.customers_id   order by invoices_id d |

 06.
| 770 | root   | localhost |              | Query   | 0    |                      | show processlist                                                                                     |

 07.
+-----+--------+-----------+--------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

 08.
# mysqladmin proc

 09.
+-----+--------+-----------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+

 10.
| Id  | User   | Host      | db           | Command | Time | State              | Info                                                                                                 |

 11.
+-----+--------+-----------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+

 12.
| 762 | revgrp | localhost | revgrp_admin | Query   | 11   | removing tmp table | SELECT * FROM invoices i, customers c where i.customers_id = c.customers_id   order by invoices_id d |

 13.
| 772 | root   | localhost |              | Query   | 0    |                    | show processlist                                                                                     |

 14.
+-----+--------+-----------+--------------+---------+------+--------------------+------------------------------------------------------------------------------------------------------+


EXPLAIN:


mysql> EXPLAIN EXTENDED SELECT *  FROM invoices i, customers c WHERE i.customers_id = c.customers_id ORDER BY invoices_id DESC;
+----+-------------+-------+------+---------------+--------------+---------+-----------------------------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref                         | rows | filtered | Extra                           |
+----+-------------+-------+------+---------------+--------------+---------+-----------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | c     | ALL  | customers_id  | NULL         | NULL    | NULL                        | 1003 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | customers_id  | customers_id | 4       | revgrp_admin.c.customers_id |   30 |   101.76 |                                 |
+----+-------------+-------+------+---------------+--------------+---------+-----------------------------+------+----------+---------------------------------+

CHANGE INDEX invoices_id to PRIMARY KEY.


EXPLAIN:

01.
mysql> EXPLAIN EXTENDED SELECT *  FROM invoices i, customers c WHERE i.customers_id = c.customers_id ORDER BY invoices_id DESC;

 02.
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+----------+----------------+

 03.
| id | select_type | table | type   | possible_keys | key     | key_len | ref                         | rows  | filtered | Extra          |

 04.
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+----------+----------------+

 05.
|  1 | SIMPLE      | i     | ALL    | customers_id  | NULL    | NULL    | NULL                        | 25517 |   100.00 | Using filesort |

 06.
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | revgrp_admin.i.customers_id |     1 |   100.00 |                |

 07.
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-------+----------+----------------+

 08.
2 rows in set, 1 warning (0.00 sec)