Guest User

Untitled

a guest
Sep 21st, 2015
76
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. QUERY:
  2.  
  3. SELECT orders_products.order_id FROM orders_products INNER JOIN orders ON orders.id = orders_products.order_id WHERE orders_products.productcode = 26232 AND orders.paid = 1;
  4.  
  5. EXPLAIN:
  6.  
  7. +----+-------------+-----------------+------+--------------------+--------+---------+------------------------+-------+-------------+
  8. | id | select_type | table           | type | possible_keys      | key    | key_len | ref                    | rows  | Extra       |
  9. +----+-------------+-----------------+------+--------------------+--------+---------+------------------------+-------+-------------+
  10. |  1 | SIMPLE      | orders          | ALL  | PRIMARY            | NULL   | NULL    | NULL                   | 24983 | Using where |
  11. |  1 | SIMPLE      | orders_products | ref  | Search,productcode | Search | 3       | mydatabase.orders.id   |     1 | Using where |
  12. +----+-------------+-----------------+------+--------------------+--------+---------+------------------------+-------+-------------+
  13.  
  14. SHOW CREATE TABLE orders:
  15.  
  16. CREATE TABLE `orders` (
  17.   `id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
  18.   `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  19.   `client_name` varchar(50) NOT NULL DEFAULT '',
  20.   `client_vatnumber` varchar(16) DEFAULT NULL,
  21.   `client_address1` varchar(100) NOT NULL DEFAULT '',
  22.   `client_address2` varchar(50) NOT NULL DEFAULT '',
  23.   `client_email` varchar(80) NOT NULL DEFAULT '',
  24.   `client_phone` varchar(30) NOT NULL DEFAULT '',
  25.   `client_birthday` date DEFAULT NULL,
  26.   `referencenumber` varchar(20) NOT NULL DEFAULT '',
  27.   `info` text,
  28.   `totalprice` decimal(9,2) NOT NULL DEFAULT '0.00',
  29.   `added` datetime NOT NULL,
  30.   `paid` tinyint(1) NOT NULL DEFAULT '0',
  31.   PRIMARY KEY (`id`),
  32.   KEY `Search` (`status`,`client_email`,`referencenumber`,`totalprice`),
  33.   KEY `client_name` (`client_name`)
  34. ) ENGINE=MyISAM AUTO_INCREMENT=47974 DEFAULT CHARSET=latin1
  35.  
  36. SHOW CREATE TABLE orders_products:
  37.  
  38. CREATE TABLE `orders_products` (
  39.   `ProductOrderID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  40.   `order_id` mediumint(7) unsigned NOT NULL DEFAULT '0',
  41.   `count` tinyint(1) NOT NULL DEFAULT '0',
  42.   `productcode` varchar(10) NOT NULL DEFAULT '',
  43.   `prodtype` tinyint(3) unsigned NOT NULL DEFAULT '0',
  44.   `name` varchar(80) NOT NULL DEFAULT '',
  45.   `price` decimal(9,2) NOT NULL DEFAULT '0.00',
  46.   `ProductReleaseDate` date DEFAULT '0000-00-00',
  47.   PRIMARY KEY (`ProductOrderID`),
  48.   KEY `Search` (`order_id`,`price`,`name`),
  49.   KEY `productcode` (`productcode`),
  50. ) ENGINE=MyISAM AUTO_INCREMENT=61460 DEFAULT CHARSET=latin1
RAW Paste Data