Jump to content

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


zq29

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.