Link to home
Start Free TrialLog in
Avatar of hassler
hassler

asked on

MySQL Lock up

Recently upgraded our server to Debian 4.0, Apache 2.0 and Mysql 5.0.32-Debian_7etch8-log. Among other things, the previous server setup was locking up (tight), but we could never determine why. Now at least, we're able to get a lot more information, and can see (clearly, I think) that it is a database issue that is locking up our web site (and previously, the entire server). However, I'm at a loss to explain why.

The attached "mysqladmin processlist" output shows the situation in the early stages of a lockup. At this point, the web site is completely unresponsive, as the web queries are all in a "Locked" state.

My biggest question is trying to figure out WHICH of these processes is holding a lock that the other threads are waiting on. You can see the top Queries have been in a "Copying to tmp table on disk" state for over 2 minutes (they'll continue for several more, seemingly never completing. under normal circumstances, these queries complete within a few seconds -- even though they may be analyzing about 3M records.

Are their obvious clues to the more-knowledgable out there that I can pursue, or any recommendations? The web site is fairly straightforward, with mostly SELECT-based queries. A fraction of traffic may store query results in a Results table (you can see some of those in the output) where previous results are DELETEd, and the INSERT INTO Results... SELECT to get the next set of results. There is ongoing administrative activity as well (INSERTion of new records and UPDATEs to existing information). We'll often see one of those SQL statements involved in this morass as well, although not in this particular case shown.

So, I believe something has a critical table locked, or deadlocked for some reason.
+--------+------+-------------------+-----+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host              | db  | Command | Time | State                        | Info                                                                                                 |
+--------+------+-------------------+-----+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 836537 | usr  | www.XXX.com:34403 | usr | Sleep   | 422  |                              |                                                                                                      |
| 837979 | usr  | www.XXX.com:35847 | usr | Sleep   | 273  |                              |                                                                                                      |
| 837981 | usr  | www.XXX.com:35849 | usr | Sleep   | 273  |                              |                                                                                                      |
| 838711 | usr  | www.XXX.com:36581 | usr | Sleep   | 231  |                              |                                                                                                      |
| 838734 | usr  | www.XXX.com:36604 | usr | Sleep   | 227  |                              |                                                                                                      |
| 838882 | usr  | www.XXX.com:36752 | usr | Sleep   | 208  |                              |                                                                                                      |
| 839044 | usr  | www.XXX.com:36913 | usr | Sleep   | 187  |                              |                                                                                                      |
| 839155 | usr  | www.XXX.com:45011 | usr | Sleep   | 180  |                              |                                                                                                      |
| 839269 | usr  | www.XXX.com:45125 | usr | Sleep   | 167  |                              |                                                                                                      |
| 839361 | usr  | www.XXX.com:45217 | usr | Sleep   | 154  |                              |                                                                                                      |
| 839364 | usr  | www.XXX.com:45220 | usr | Sleep   | 154  |                              |                                                                                                      |
| 839373 | usr  | www.XXX.com:45229 | usr | Sleep   | 152  |                              |                                                                                                      |
| 839375 | usr  | www.XXX.com:45231 | usr | Sleep   | 152  |                              |                                                                                                      |
| 839420 | usr  | www.XXX.com:45276 | usr | Query   | 95   | Locked                       | INSERT INTO Results (itemID, lead, auctionid, saledate, price, score, namescore, sessionID) SELECT   |
| 839423 | usr  | www.XXX.com:45279 | usr | Query   | 146  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839425 | usr  | www.XXX.com:45281 | usr | Query   | 146  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839430 | usr  | www.XXX.com:45286 | usr | Query   | 145  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839432 | usr  | www.XXX.com:45288 | usr | Query   | 145  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839438 | usr  | www.XXX.com:45294 | usr | Query   | 142  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839440 | usr  | www.XXX.com:45296 | usr | Query   | 141  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839443 | usr  | www.XXX.com:45299 | usr | Query   | 140  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839445 | usr  | www.XXX.com:45301 | usr | Query   | 140  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839448 | usr  | www.XXX.com:45304 | usr | Sleep   | 133  |                              |                                                                                                      |
| 839473 | usr  | www.XXX.com:45329 | usr | Sleep   | 127  |                              |                                                                                                      |
| 839572 | usr  | www.XXX.com:45430 | usr | Sleep   | 116  |                              |                                                                                                      |
| 839588 | usr  | www.XXX.com:45446 | usr | Query   | 113  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839589 | usr  | www.XXX.com:45447 | usr | Query   | 113  | Locked                       | INSERT INTO Items (lead,origin,yearlow,yearhigh,description,provenance,`condition`,dimensions,litera |
| 839596 | usr  | www.XXX.com:45454 | usr | Query   | 110  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839597 | usr  | www.XXX.com:45455 | usr | Query   | 108  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839601 | usr  | www.XXX.com:45459 | usr | Query   | 105  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839603 | usr  | www.XXX.com:45461 | usr | Query   | 104  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839604 | usr  | www.XXX.com:45462 | usr | Query   | 103  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839606 | usr  | www.XXX.com:45464 | usr | Query   | 100  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839609 | usr  | www.XXX.com:45467 | usr | Query   | 96   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839610 | usr  | www.XXX.com:45468 | usr | Query   | 96   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839613 | usr  | www.XXX.com:45471 | usr | Query   | 95   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839614 | usr  | www.XXX.com:45472 | usr | Query   | 95   | Locked                       | SELECT itemID, lead FROM Items WHERE itemID=224241                                                   |
| 839616 | usr  | www.XXX.com:45474 | usr | Query   | 92   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839617 | usr  | www.XXX.com:45475 | usr | Query   | 91   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839618 | usr  | www.XXX.com:45476 | usr | Query   | 88   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839620 | usr  | www.XXX.com:45478 | usr | Query   | 88   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839623 | usr  | www.XXX.com:45481 | usr | Query   | 83   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839625 | usr  | www.XXX.com:45483 | usr | Query   | 83   | Locked                       | SELECT /* Search.pm:200 */ DISTINCT Items.itemID,Items.lead,ItemHistory.saleDate,ItemHistory.price F |
| 839626 | usr  | www.XXX.com:45484 | usr | Query   | 82   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839627 | usr  | www.XXX.com:45485 | usr | Query   | 81   | Locked                       | UPDATE Items SET  lead='Table-Drop-Leaf; George III, Mahogany, Demilune Leaves, 1 Apron Drawer, Turn |
| 839630 | usr  | www.XXX.com:45488 | usr | Query   | 76   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839632 | usr  | www.XXX.com:45490 | usr | Query   | 74   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839633 | usr  | www.XXX.com:45491 | usr | Query   | 72   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839646 | usr  | www.XXX.com:45504 | usr | Query   | 72   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839648 | usr  | www.XXX.com:45506 | usr | Query   | 70   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839651 | usr  | www.XXX.com:45509 | usr | Query   | 66   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839652 | usr  | www.XXX.com:45511 | usr | Query   | 66   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839654 | usr  | www.XXX.com:45513 | usr | Query   | 65   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839656 | usr  | www.XXX.com:45515 | usr | Query   | 65   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839658 | usr  | www.XXX.com:45517 | usr | Query   | 64   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839660 | usr  | www.XXX.com:45519 | usr | Query   | 60   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839661 | usr  | www.XXX.com:45520 | usr | Query   | 60   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839663 | usr  | www.XXX.com:45522 | usr | Query   | 60   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839665 | usr  | www.XXX.com:45524 | usr | Query   | 58   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839666 | usr  | www.XXX.com:45525 | usr | Query   | 57   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839667 | usr  | www.XXX.com:45526 | usr | Query   | 56   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839669 | usr  | www.XXX.com:45528 | usr | Query   | 55   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839672 | usr  | www.XXX.com:45531 | usr | Query   | 50   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839675 | usr  | www.XXX.com:45534 | usr | Query   | 49   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839676 | usr  | www.XXX.com:45535 | usr | Query   | 49   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839677 | usr  | www.XXX.com:45536 | usr | Sleep   | 16   |                              |                                                                                                      |
| 839678 | usr  | www.XXX.com:45537 | usr | Sleep   | 10   |                              |                                                                                                      |
| 839679 | usr  | www.XXX.com:45538 | usr | Query   | 46   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839680 | usr  | www.XXX.com:45539 | usr | Sleep   | 5    |                              |                                                                                                      |
| 839682 | usr  | www.XXX.com:45541 | usr | Query   | 45   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839683 | usr  | www.XXX.com:45542 | usr | Query   | 44   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839686 | usr  | www.XXX.com:45545 | usr | Query   | 40   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839687 | usr  | www.XXX.com:45546 | usr | Query   | 40   | Locked                       | DELETE FROM Results WHERE sessionID='0f9ab21f44053104308e8f40bff59ec4'                               |
| 839689 | usr  | www.XXX.com:45548 | usr | Query   | 40   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839690 | usr  | www.XXX.com:45549 | usr | Query   | 39   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839692 | usr  | www.XXX.com:45551 | usr | Query   | 39   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839694 | usr  | www.XXX.com:45553 | usr | Query   | 38   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839695 | usr  | www.XXX.com:45554 | usr | Query   | 34   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839696 | usr  | www.XXX.com:45555 | usr | Query   | 33   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839697 | usr  | www.XXX.com:45556 | usr | Query   | 31   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839699 | usr  | www.XXX.com:45558 | usr | Query   | 31   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839701 | usr  | www.XXX.com:45560 | usr | Query   | 30   | Locked                       | SELECT itemID FROM Items WHERE itemID>=192012 AND itemID<192112                                      |
| 839703 | usr  | www.XXX.com:45562 | usr | Query   | 29   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839705 | usr  | www.XXX.com:45564 | usr | Query   | 25   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839708 | usr  | www.XXX.com:45567 | usr | Query   | 23   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839710 | usr  | www.XXX.com:45569 | usr | Query   | 22   | Locked                       | SELECT /* Search.pm:200 */ DISTINCT Items.itemID,Items.lead,ItemHistory.saleDate,ItemHistory.price F |
| 839712 | usr  | www.XXX.com:45571 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839713 | usr  | www.XXX.com:45572 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839714 | usr  | www.XXX.com:45573 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839715 | usr  | www.XXX.com:45574 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839716 | usr  | www.XXX.com:45575 | usr | Query   | 19   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839718 | usr  | www.XXX.com:45577 | usr | Query   | 17   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839720 | usr  | www.XXX.com:45579 | usr | Query   | 14   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839723 | usr  | www.XXX.com:45582 | usr | Query   | 8    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839725 | usr  | www.XXX.com:45584 | usr | Query   | 5    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839726 | usr  | www.XXX.com:45585 | usr | Query   | 5    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839728 | usr  | www.XXX.com:45588 | usr | Query   | 2    | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839735 | usr  | www.XXX.com:45596 | usr | Sleep   | 0    |                              |                                                                                                      |
| 839737 | usr  | www.XXX.com:45598 | usr | Query   | 0    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839738 | usr  | localhost         |     | Query   | 0    |                              | show processlist                                                                                     |
+--------+------+-------------------+-----+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+

Open in new window

Avatar of tomh111699
tomh111699

You really want to avoid the need for creating temp table on disk.

I would start by tuning my sort buffer size, or isolating the query that leads to the file sort.

Is the order by clause needed. I am assuming there is an order by.

Is there a group by, group by row leads to an implicit order by row.

Can you split that query into separate queries.

When  file sort happens Mysql joins the tables and attempts to create a memory table to sort the results. When it runs out of memory it creates a disk table. All the while it still has the tables locked.

Innodb locks at te row level, myisam locks at the table level. Tune that query.

Are indexes being used ?

Run "explain" on the query. to see if indexes are being used.
http://dev.mysql.com/doc/refman/5.1/en/explain.html

If adding indexes does not help increase your temp_table_size in MySQL and your sort buffer.

Show us the output of explain for those queries that we see writing temp table to disk.
ASKER CERTIFIED SOLUTION
Avatar of hassler
hassler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hassler

ASKER

following is a full query like the ones shown above as the "SELECT count(DISTINCT..." as well as the explain output from it.
mysql> explain SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory.price,MATCH (lead,Items.description,provenance,`condition`) AGAINST ('\"kay starr\"' IN BOOLEAN MODE),0,"03b484a37cfdab4d3627f504aa25ccd3") as count FROM Items LEFT JOIN ItemCategories ON ItemCategories.itemID = Items.itemID LEFT JOIN ItemHistory ON Items.itemID=ItemHistory.itemID LEFT JOIN Auctions ON ItemHistory.auctionID=Auctions.auctionID LEFT JOIN AuctionHouses ON Auctions.houseID=AuctionHouses.houseID WHERE ItemCategories.category='autographs' AND MATCH (lead,Items.description,provenance,`condition`) AGAINST ('\"kay starr\"' IN BOOLEAN MODE) AND (ItemHistory.saleDate < CURRENT_DATE()) AND Items.publish='Y' ORDER BY ItemHistory.saleDate DESC;
+----+-------------+----------------+----------+----------------------------------------------+---------------+---------+---------------------------+------+------------------------------+
| id | select_type | table          | type     | possible_keys                                | key           | key_len | ref                       | rows | Extra                        |
+----+-------------+----------------+----------+----------------------------------------------+---------------+---------+---------------------------+------+------------------------------+
|  1 | SIMPLE      | Items          | fulltext | PRIMARY,idx_publish,lead                     | lead          | 0       |                           |    1 | Using where; Using temporary | 
|  1 | SIMPLE      | ItemCategories | ref      | idx_itemprior,idx_itemID,idx_cat,idx_cattype | idx_itemprior | 4       | p4a.Items.itemID          |    1 | Using where                  | 
|  1 | SIMPLE      | ItemHistory    | ref      | itemID,idx_saledate                          | itemID        | 4       | p4a.ItemCategories.itemID |    1 | Using where                  | 
|  1 | SIMPLE      | Auctions       | eq_ref   | PRIMARY                                      | PRIMARY       | 4       | p4a.ItemHistory.auctionID |    1 |                              | 
|  1 | SIMPLE      | AuctionHouses  | eq_ref   | PRIMARY                                      | PRIMARY       | 4       | p4a.Auctions.houseID      |    1 | Using index                  | 
+----+-------------+----------------+----------+----------------------------------------------+---------------+---------+---------------------------+------+------------------------------+
5 rows in set (0.00 sec)

Open in new window