Jump to content


Photo

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


  • Please log in to reply
6 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 September 2006 - 04:17 PM

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:
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
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:
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

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 September 2006 - 05:02 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 September 2006 - 05:57 PM

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 September 2006 - 06:17 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 September 2006 - 06:36 PM

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.

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...

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 September 2006 - 07:01 PM

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

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
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".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 06 September 2006 - 07:16 PM

Ahhh, I see! Thanks a bunch Fenway :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users