Jump to content

Archived

This topic is now archived and is closed to further replies.

Kris

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

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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...

Share this post


Link to post
Share on other sites
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".

Share this post


Link to post
Share on other sites
Ahhh, I see! Thanks a bunch Fenway :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.