Two similar queries - One times out, other one instant. Ideas?

[zq29]

Hi guys, I'm having a problem with a query that is generated by some PHP code. Here's the first which pretty much just crashes my machine, I have to kill the mysql process to get my machine back in a useable state:
[code]SELECT DISTINCT(l.`company`), l.`id`, l.`bullets`, l.`level`, l.`address_1`, l.`address_2`, l.`town`, l.`postcode`, l.`telephone`, l.`email`, l.`map`, l.`website`
FROM `listings` as l, `listing_category` as lc, `categories` as c
WHERE ((l.`company` LIKE '% car %' OR l.`company` LIKE 'car %' OR l.`company` LIKE '% car' OR l.`company` LIKE 'car'))
ORDER BY l.`level` DESC, l.`top` ASC, l.`company` ASC[/code]
And here's the second query, that is pretty much the same as the one above, except that it queries a different table, this one returns results instantly:
[code]SELECT DISTINCT(l.`company`), l.`id`, l.`bullets`, l.`level`, l.`address_1`, l.`address_2`, l.`town`, l.`postcode`, l.`telephone`, l.`email`, l.`map`, l.`website`
FROM `listings` as l, `listing_category` as lc, `categories` as c
WHERE (((c.`name` LIKE '% car %' OR c.`name` LIKE 'car %' OR c.`name` LIKE '% car' OR c.`name` LIKE 'car') AND c.`id`=lc.`category_id` AND lc.`listing_id`=l.`id`))
ORDER BY l.`level` DESC, l.`top` ASC, l.`company` ASC[/code]

Can anyone offer any insight as to why the first one is giving me problems?

Oh, the first table holds around 3000 records, the second about 500. Could this be the reason? If so, any ideas on how to optimise the queries?

[fenway]

There a bunch of reasons -- though the most obvious is that your JOIN condition is absent (which would be apparent if it where in proper JOIN syntax, which I highly recommend).  Moreover, DISTINCT will do nothing here, since it's not a function, and you're getting back the ID, meaning all rows will be distinct anyway.  Also, by using DESC in your ORDER BY, you won't be able to use an index, if it were available to begin with.  Finally, having 4 LIKE clauses ORed isn't particularly pretty either -- you could probably simplify this with a single regex.

[zq29]

Thanks Fenway, it was indeed the lack of a join condition that was the main cause of my problem. I wouldn't have thought that would have been a problem, but it obviously was.

[fenway]

No problem... you were simply returning N**3 rows.  I've done that before, on a 4-table join, before I switched to join syntax, and accidentally return >9 trillion records.  That'll take down a server real fast, trust me.

[zq29]

[quote author=fenway link=topic=107126.msg429417#msg429417 date=1157566652]
No problem... you were simply returning N**3 rows.  I've done that before, on a 4-table join, before I switched to join syntax, and accidentally return >9 trillion records.  That'll take down a server real fast, trust me.
[/quote]All I did was remove references to tables I wasn't actually using from the 'FROM' part of my query. Would it be possible for you to elaborate on what you mean by 'join syntax'? I've done a bit of searching here and there and I can only find references to left and right joins, and I don't think that is what I need here...

[fenway]

Sure... take your second query (I've left it in your formatting):

[code]
SELECT DISTINCT(l.`company`), l.`id`, l.`bullets`, l.`level`, l.`address_1`, l.`address_2`, l.`town`, l.`postcode`, l.`telephone`, l.`email`, l.`map`, l.`website`
FROM `listings` as l
INNER JOIN `listing_category` as lc ON ( lc.`listing_id`=l.`id`)
INNER JOIN `categories` as c ON (c.`id`=lc.`category_id`)
WHERE (c.`name` LIKE '% car %' OR c.`name` LIKE 'car %' OR c.`name` LIKE '% car' OR c.`name` LIKE 'car')
ORDER BY l.`level` DESC, l.`top` ASC, l.`company` ASC
[/code]
That way, not only can see how many you have, but if you remove the table, you remove the ON clause, and vice versa, so your WHERE clause doesn't get "polluted".

[zq29]

Ahhh, I see! Thanks a bunch Fenway :)