Skip to content

Instantly share code, notes, and snippets.

@danahern

danahern/r_mysql_explain_for_s

Created March 10, 2009 17:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danahern/77026 to your computer and use it in GitHub Desktop.
Save danahern/77026 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
r_mysql_explain_for_s
mysql> explain select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id LEFT JOIN categories on meta_fields.category_id = categories.id where categorizable_type = 'Listing' and categorizable_id in (SELECT distinct(`listings`.id) FROM `listings` where (expires_on IS NOT NULL)) and categories.lft BETWEEN 34204 and 35319 group by category_specific_fields.field_name, meta_fields.value;
+----+--------------------+--------------------------+-----------------+---------------------------------+---------+---------+----------------------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------------+-----------------+---------------------------------+---------+---------+----------------------------------------------------------------+--------+----------------------------------------------+
| 1 | PRIMARY | meta_fields | ALL | NULL | NULL | NULL | NULL | 209015 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | categories | eq_ref | PRIMARY,index_categories_on_lft | PRIMARY | 4 | classifieds_development.meta_fields.category_id | 1 | Using where |
| 1 | PRIMARY | category_specific_fields | eq_ref | PRIMARY | PRIMARY | 4 | classifieds_development.meta_fields.category_specific_field_id | 1 | |
| 2 | DEPENDENT SUBQUERY | listings | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+--------------------------+-----------------+---------------------------------+---------+---------+----------------------------------------------------------------+--------+----------------------------------------------+
4 rows in set (0.00 sec)
Raw
s_removed_the_join
mysql> select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields
-> LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id
-> LEFT JOIN categories on meta_fields.category_id = categories.id
-> where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT distinct(`listings`.id) FROM `listings` where (expires_on IS NOT NULL))
-> and categories.lft BETWEEN 34204 and 35319
-> group by category_specific_fields.field_name, meta_fields.value;
+----------+---------------+--------------------+
| count(*) | field_name | value |
+----------+---------------+--------------------+
| 1167 | 1st. Edition | 0 |
| 1198 | 1st. Edition | 1 |
| 242 | Binding | Fine |
| 233 | Binding | Hardcover |
| 217 | Binding | Hardcover w/Jacket |
| 240 | Binding | Leather |
| 235 | Binding | Letter |
| 232 | Binding | Manuscript/Unbound |
| 214 | Binding | Pamphlet |
| 254 | Binding | Postcard |
| 257 | Binding | Softcover |
| 241 | Binding | Vellum |
| 1152 | Fictional | 0 |
| 1213 | Fictional | 1 |
| 1202 | Non-Fictional | 0 |
| 1163 | Non-Fictional | 1 |
| 276 | Printing Year | 1501-1699 |
| 298 | Printing Year | 1700-1799 |
| 298 | Printing Year | 1800-1849 |
| 296 | Printing Year | 1850-1899 |
| 305 | Printing Year | 1900-1949 |
| 294 | Printing Year | 1950-1999 |
| 291 | Printing Year | 2000-Now |
| 307 | Printing Year | Pre-1500 |
| 1203 | Signed | 0 |
| 1162 | Signed | 1 |
+----------+---------------+--------------------+
26 rows in set (1.07 sec)
Raw
t_explain_from_u
mysql> explain select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id LEFT JOIN categories on meta_fields.category_id = categories.id where categorizable_type = 'Listing' and categorizable_id in (SELECT distinct(`listings`.id) FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 34204 AND 35319) AND (expires_on IS NOT NULL))) and categories.lft BETWEEN 34204 and 35319 group by category_specific_fields.field_name, meta_fields.value with rollup;
+----+--------------------+--------------------------+--------+---------------------------------------------------+---------+---------+----------------------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------------+--------+---------------------------------------------------+---------+---------+----------------------------------------------------------------+--------+----------------------------------------------+
| 1 | PRIMARY | meta_fields | ALL | NULL | NULL | NULL | NULL | 209015 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | categories | eq_ref | PRIMARY,index_categories_on_lft | PRIMARY | 4 | classifieds_development.meta_fields.category_id | 1 | Using where |
| 1 | PRIMARY | category_specific_fields | eq_ref | PRIMARY | PRIMARY | 4 | classifieds_development.meta_fields.category_specific_field_id | 1 | |
| 2 | DEPENDENT SUBQUERY | categorizations | ref | index_categorizations_on_category_lft,ccict,cctci | ccict | 263 | func,const | 1 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | listings | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+--------------------------+--------+---------------------------------------------------+---------+---------+----------------------------------------------------------------+--------+----------------------------------------------+
5 rows in set (0.00 sec)
Raw
u_this_be_it
mysql> select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields
-> LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id
-> LEFT JOIN categories on meta_fields.category_id = categories.id
-> where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT distinct(`listings`.id) FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 34204 AND 35319) AND (expires_on IS NOT NULL)))
-> and categories.lft BETWEEN 34204 and 35319
-> group by category_specific_fields.field_name, meta_fields.value with rollup
-> ;
+----------+---------------+--------------------+
| count(*) | field_name | value |
+----------+---------------+--------------------+
| 1163 | 1st. Edition | 0 |
| 1191 | 1st. Edition | 1 |
| 2354 | 1st. Edition | NULL |
| 241 | Binding | Fine |
| 230 | Binding | Hardcover |
| 217 | Binding | Hardcover w/Jacket |
| 237 | Binding | Leather |
| 235 | Binding | Letter |
| 232 | Binding | Manuscript/Unbound |
| 214 | Binding | Pamphlet |
| 253 | Binding | Postcard |
| 254 | Binding | Softcover |
| 241 | Binding | Vellum |
| 2354 | Binding | NULL |
| 1149 | Fictional | 0 |
| 1205 | Fictional | 1 |
| 2354 | Fictional | NULL |
| 1198 | Non-Fictional | 0 |
| 1156 | Non-Fictional | 1 |
| 2354 | Non-Fictional | NULL |
| 274 | Printing Year | 1501-1699 |
| 296 | Printing Year | 1700-1799 |
| 298 | Printing Year | 1800-1849 |
| 296 | Printing Year | 1850-1899 |
| 305 | Printing Year | 1900-1949 |
| 292 | Printing Year | 1950-1999 |
| 289 | Printing Year | 2000-Now |
| 304 | Printing Year | Pre-1500 |
| 2354 | Printing Year | NULL |
| 1198 | Signed | 0 |
| 1156 | Signed | 1 |
| 2354 | Signed | NULL |
| 14124 | NULL | NULL |
+----------+---------------+--------------------+
33 rows in set (3.95 sec)
Raw
v_this_is_it__I_think
mysql> select count(*), meta_fields.category_specific_field_id as csf_id, category_specific_fields.field_name, meta_fields.value from meta_fields
-> LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id
-> LEFT JOIN categories on meta_fields.category_id = categories.id
-> where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT `listings`.id FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 34204 AND 35319) AND (expires_on IS NOT NULL)))
-> and categories.lft BETWEEN 34204 and 35319
-> group by category_specific_field_id, meta_fields.value with rollup
->
->
->
-> ;
+----------+--------+---------------+--------------------+
| count(*) | csf_id | field_name | value |
+----------+--------+---------------+--------------------+
| 241 | 141 | Binding | Fine |
| 230 | 141 | Binding | Hardcover |
| 217 | 141 | Binding | Hardcover w/Jacket |
| 237 | 141 | Binding | Leather |
| 235 | 141 | Binding | Letter |
| 232 | 141 | Binding | Manuscript/Unbound |
| 214 | 141 | Binding | Pamphlet |
| 253 | 141 | Binding | Postcard |
| 254 | 141 | Binding | Softcover |
| 241 | 141 | Binding | Vellum |
| 2354 | 141 | Binding | NULL |
| 1198 | 142 | Signed | 0 |
| 1156 | 142 | Signed | 1 |
| 2354 | 142 | Signed | NULL |
| 1163 | 143 | 1st. Edition | 0 |
| 1191 | 143 | 1st. Edition | 1 |
| 2354 | 143 | 1st. Edition | NULL |
| 274 | 144 | Printing Year | 1501-1699 |
| 296 | 144 | Printing Year | 1700-1799 |
| 298 | 144 | Printing Year | 1800-1849 |
| 296 | 144 | Printing Year | 1850-1899 |
| 305 | 144 | Printing Year | 1900-1949 |
| 292 | 144 | Printing Year | 1950-1999 |
| 289 | 144 | Printing Year | 2000-Now |
| 304 | 144 | Printing Year | Pre-1500 |
| 2354 | 144 | Printing Year | NULL |
| 1149 | 169 | Fictional | 0 |
| 1205 | 169 | Fictional | 1 |
| 2354 | 169 | Fictional | NULL |
| 1198 | 170 | Non-Fictional | 0 |
| 1156 | 170 | Non-Fictional | 1 |
| 2354 | 170 | Non-Fictional | NULL |
| 14124 | NULL | Non-Fictional | NULL |
+----------+--------+---------------+--------------------+
33 rows in set (3.26 sec)
Raw
w_Something Fishy
mysql> select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields -> LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id -> LEFT JOIN categories on meta_fields.category_id = categories.id
-> where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT `listings`.id FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 34204 AND 35319) AND (expires_on IS NOT NULL)))
-> and categories.lft BETWEEN 34204 and 35319
-> group by category_specific_field_id, category_specific_value_id with rollup
-> ;
+----------+---------------+--------------------+
| count(*) | field_name | value |
+----------+---------------+--------------------+
| 241 | Binding | Fine |
| 230 | Binding | Hardcover |
| 217 | Binding | Hardcover w/Jacket |
| 237 | Binding | Leather |
| 232 | Binding | Manuscript/Unbound |
| 214 | Binding | Pamphlet |
| 254 | Binding | Softcover |
| 241 | Binding | Vellum |
| 253 | Binding | Postcard |
| 235 | Binding | Letter |
| 2354 | Binding | Letter |
| 2354 | Signed | 1 |
| 2354 | Signed | 1 |
| 2354 | 1st. Edition | 1 |
| 2354 | 1st. Edition | 1 |
| 304 | Printing Year | Pre-1500 |
| 274 | Printing Year | 1501-1699 |
| 296 | Printing Year | 1700-1799 |
| 298 | Printing Year | 1800-1849 |
| 296 | Printing Year | 1850-1899 |
| 305 | Printing Year | 1900-1949 |
| 292 | Printing Year | 1950-1999 |
| 289 | Printing Year | 2000-Now |
| 2354 | Printing Year | 2000-Now |
| 2354 | Fictional | 0 |
| 2354 | Fictional | 0 |
| 2354 | Non-Fictional | 1 |
| 2354 | Non-Fictional | 1 |
| 14124 | Non-Fictional | 1 |
+----------+---------------+--------------------+
29 rows in set (4.33 sec)
Raw
x_additional_bad_assory
mysql> select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields
-> LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id
-> LEFT JOIN categories on meta_fields.category_id = categories.id
-> where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT `listings`.id FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 34204 AND 35319) AND (expires_on IS NOT NULL)))
-> and categories.lft BETWEEN 34204 and 35319
-> group by category_specific_value_id
-> ;
+----------+---------------+--------------------+
| count(*) | field_name | value |
+----------+---------------+--------------------+
| 9416 | Signed | 1 |
| 241 | Binding | Fine |
| 230 | Binding | Hardcover |
| 217 | Binding | Hardcover w/Jacket |
| 237 | Binding | Leather |
| 232 | Binding | Manuscript/Unbound |
| 214 | Binding | Pamphlet |
| 254 | Binding | Softcover |
| 241 | Binding | Vellum |
| 253 | Binding | Postcard |
| 235 | Binding | Letter |
| 304 | Printing Year | Pre-1500 |
| 274 | Printing Year | 1501-1699 |
| 296 | Printing Year | 1700-1799 |
| 298 | Printing Year | 1800-1849 |
| 296 | Printing Year | 1850-1899 |
| 305 | Printing Year | 1900-1949 |
| 292 | Printing Year | 1950-1999 |
| 289 | Printing Year | 2000-Now |
+----------+---------------+--------------------+
19 rows in set (3.95 sec)
Raw
y_bad_assosity
mysql> select count(*), category_specific_fields.field_name, meta_fields.value from meta_fields LEFT JOIN category_specific_fields on meta_fields.category_specific_field_id = category_specific_fields.id where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT `listings`.id FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 51532 AND 51579) AND (expires_on IS NOT NULL && price <= 100.0 && price >= 0.0 && images_count > 0 && title LIKE '%door%' && ((payment_by & conv('8',10,2)) = '8' || (payment_by & conv('16',10,2)) = '16' || (payment_by & conv('1',10,2)) = '1' || (payment_by & conv('2',10,2)) = '2' || (payment_by & conv('4',10,2)) = '4'))))
-> group by category_specific_value_id;
+----------+--------------------+--------------------------+
| count(*) | field_name | value |
+----------+--------------------+--------------------------+
| 14 | Bluetooth Enabled | 1 |
| 1 | Date Created | 2007 |
| 1 | Size in Inches | 23-24 |
| 1 | Authenticity | Modern Reproduction |
| 1 | Style | Art Nouveau |
| 1 | Signiture | Not Signed |
| 1 | Art Source | From Dealers/Wholesalers |
| 1 | PrimaryColor | Cream |
| 1 | Condition | Used-Average Shape |
| 1 | Item Primary Color | Yellow |
| 1 | Item Condition | Refurbished |
| 1 | Condition | Refurbished |
| 1 | Carrier | Alltel |
| 1 | Contract | Prepaid/Pay as you go |
| 1 | Band | WCDMA |
| 1 | Primary Color | Charcoal |
| 1 | Item Company Type | Budweiser |
| 1 | Type | Non-Programmable |
| 1 | Brand | Dr. Brandt Skincare |
| 1 | Brand | Guerlain |
| 1 | Brand | Borghese |
| 1 | Brand | Jan Marini |
| 1 | Brand | Nars |
| 1 | Condition | New |
+----------+--------------------+--------------------------+
24 rows in set (3.83 sec)
Raw
z_first_try
mysql> select count(*), meta_fields.* from meta_fields where categorizable_type = 'Listing' and categorizable_id in
-> (SELECT `listings`.id FROM `listings` INNER JOIN `categorizations` ON `categorizations`.categorizable_id = `listings`.id AND `categorizations`.categorizable_type = 'Listing' WHERE ((`categorizations`.`category_lft` BETWEEN 51532 AND 51579) AND (expires_on IS NOT NULL && price <= 100.0 && price >= 0.0 && images_count > 0 && title LIKE '%door%' && ((payment_by & conv('8',10,2)) = '8' || (payment_by & conv('16',10,2)) = '16' || (payment_by & conv('1',10,2)) = '1' || (payment_by & conv('2',10,2)) = '2' || (payment_by & conv('4',10,2)) = '4'))))
-> group by category_specific_value_id;
+----------+-------+-------------+----------------------------+----------------------------+------------------+--------------------------+--------------------+
| count(*) | id | category_id | category_specific_field_id | category_specific_value_id | categorizable_id | value | categorizable_type |
+----------+-------+-------------+----------------------------+----------------------------+------------------+--------------------------+--------------------+
| 14 | 3125 | 296302 | 1915 | NULL | 772 | 1 | Listing |
| 1 | 6690 | 274228 | 308 | 1113 | 1653 | 2007 | Listing |
| 1 | 6691 | 274228 | 309 | 1126 | 1653 | 23-24 | Listing |
| 1 | 6692 | 274228 | 310 | 1133 | 1653 | Modern Reproduction | Listing |
| 1 | 6693 | 274228 | 311 | 1138 | 1653 | Art Nouveau | Listing |
| 1 | 6694 | 274228 | 316 | 1188 | 1653 | Not Signed | Listing |
| 1 | 6695 | 274228 | 327 | 1238 | 1653 | From Dealers/Wholesalers | Listing |
| 1 | 9581 | 300012 | 450 | 2319 | 2415 | Cream | Listing |
| 1 | 2071 | 266616 | 837 | 6050 | 505 | Used-Average Shape | Listing |
| 1 | 5659 | 293070 | 1122 | 10988 | 1388 | Yellow | Listing |
| 1 | 5660 | 293070 | 1123 | 10995 | 1388 | Refurbished | Listing |
| 1 | 3123 | 273001 | 1833 | 65726 | 772 | Refurbished | Listing |
| 1 | 3124 | 296302 | 1914 | 66965 | 772 | Alltel | Listing |
| 1 | 3139 | 296302 | 1929 | 66990 | 772 | Prepaid/Pay as you go | Listing |
| 1 | 3140 | 296302 | 1930 | 66998 | 772 | WCDMA | Listing |
| 1 | 3141 | 296302 | 1933 | 67013 | 772 | Charcoal | Listing |
| 1 | 9817 | 280858 | 2034 | 68408 | 2485 | Budweiser | Listing |
| 1 | 5000 | 278886 | 2607 | 88848 | 1233 | Non-Programmable | Listing |
| 1 | 5001 | 276593 | 2650 | 91205 | 1233 | Dr. Brandt Skincare | Listing |
| 1 | 9579 | 276594 | 2654 | 91509 | 2415 | Guerlain | Listing |
| 1 | 10307 | 276597 | 2656 | 91637 | 2618 | Borghese | Listing |
| 1 | 2422 | 276614 | 2658 | 91822 | 602 | Jan Marini | Listing |
| 1 | 10308 | 276960 | 2684 | 92247 | 2618 | Nars | Listing |
| 1 | 9580 | 299590 | 2817 | 94771 | 2415 | New | Listing |
+----------+-------+-------------+----------------------------+----------------------------+------------------+--------------------------+--------------------+
24 rows in set (3.85 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment