Worst query ever(?) filling up slow query log...

dalecosp

I didn't write this one, nor did I design the DB ... but I'm stuck trying to figure out if we can optimize it:

SELECT count(`cid`) as tcount FROM `user_class`, 
`user_class_ads_extra`, `user_class_ads_extra` as extratable0, 
`user_class_ads_extra` as extratable1, `user_class_ads_extra` as extratable2, 
`user_class_ads_extra` as extratable3, `user_class_ads_extra` as extratable4, 
`user_class_ads_extra` as extratable5, `user_class_ads_extra` as extratable6, 
`user_class_ads_extra` as extratable7 

WHERE `user_class`.`id` = 
`user_class_ads_extra`.`classified_id`  AND `user_class`.`id` = 
`extratable0`.`classified_id`  AND `user_class`.`id` = 
`extratable1`.`classified_id`  AND `user_class`.`id` = 
`extratable2`.`classified_id`  AND `user_class`.`id` = 
`extratable3`.`classified_id`  AND `user_class`.`id` = 
`extratable4`.`classified_id`  AND `user_class`.`id` = 
`extratable5`.`classified_id`  AND `user_class`.`id` = 
`extratable6`.`classified_id`  AND `user_class`.`id` = 
`extratable7`.`classified_id`  AND ((`user_class_ads_extra`.name='Color' AND 
`user_class_ads_extra`.value='Red')) AND (extratable0.value='Chevy') AND 
(extratable1.value='Used') AND (extratable2.value='Used') AND 
(extratable3.value='Chevy') AND (extratable4.value='Chevy') AND 
(extratable5.value='Chevy') AND (extratable6.value='4WD') AND 
(extratable7.value='100000-130000') AND `live`=1 AND seller = '2588';


Any thoughts?

Meanwhile, I'm off to find the lame PHP that *generated* this, I guess, and figure out WITH it's *supposed* to be doing....

Weedpacket

My first thought is that there's a site for that sort of thing.

My second thought is: "if this query is generated, then what good would optimising it do, when it's only going to be generated again?"

My third thought:

classified_id | name | value
--------------+------+------
42            | Color| Red
42            | ?    | Chevy
42            | ?    | Used
42            | ?    | 100000-130000
42            | ?    | 4WD

Is that it's a wonderfully flexible schema: it can store an arbitrary number of arbitrary properties for any classified whatever - and all in an easy-to-search format. So if you are looking for an insipid maroon 750hp bathtub in the 40-50cent range you can just build a query to search for each of 'insipid', 'maroon', '750hp', 'bathtub', '40-50cent', and find all the classified_ids that appear in all of the returned result sets.

dalecosp

Excellent suggestion. I've no idea how to go about it though. Unfortunately, database conversion of the entire site isn't on the project list (36 new/open bugs are 2nd place, too). I have this thread in the Bugs DB though, so your suggestion will be available for discussion and potential implementation.

I mean, the VP says, "we know we'll have to update someday". But I think he believes this to be 12 months+ in the future. Meantime, I'm trying to keep a 2-CPU CentOS box ahead of the curve, which is pointing "up" in excess of 30 degrees for both server side and client side stats.

Hopefully that "profit sharing" will kick in soon - I may need my vacation to be someplace besides home.... 😉

dalecosp

Interestingly enough, the schema seems to be similar already:

+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| classified_id | int(11)    | NO   | MUL | 0       |       |
| name          | tinytext   | NO   |     | NULL    |       |
| question_id   | int(11)    | NO   |     | 0       |       |
| value         | text       | NO   |     | NULL    |       |
| explanation   | tinytext   | NO   |     | NULL    |       |
| checkbox      | tinyint(4) | NO   |     | 0       |       |
| group_id      | int(11)    | NO   |     | 0       |       |
| display_order | int(11)    | NO   |     | 0       |       |
+---------------+------------+------+-----+---------+-------+


+---------------+-------------+-------------+-------+----------------------------+----------+----------+---------------+
| classified_id | name        | question_id | value | explanation                | checkbox | group_id | display_order |
+---------------+-------------+-------------+-------+----------------------------+----------+----------+---------------+
|        166848 | Maker       |         312 | Chevy |                            |        0 |        0 |             5 |
|         59905 | Mileage     |         165 | 0     |                            |        0 |        0 |            25 |
|        150628 | Condition   |         320 | New   | Current condition of auto. |        0 |        0 |             7 |
+---------------+-------------+-------------+-------+----------------------------+----------+----------+---------------+

Any suggestions how the query might be changed/optimized? Why all those JOINs just to get a count?