Jump to content


Photo

count an inner join query


  • Please log in to reply
1 reply to this topic

#1 vexon

vexon
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 24 October 2006 - 07:13 PM

hello :)

I'm trying to count make a results page where you choose the amount of results shown per page. Also I want there to be a dropdown box to choose a page with results for example ranging from 100 tot 130 (Limit 100, 130) and a back and forward button.

To do this I'm making a query that counts all the rows without the limit so I can calculate the amount of page etc.
The query I'm using for this is:

$sql2 = "SELECT pricelist.description, pricelist.ref_trac, pricelist.old_ref, pricelist.PRICE_ID, pricelist.bruto_prices, OEM.OEM, COUNT(*) FROM pricelist INNER JOIN OEM ON pricelist.ref_trac = OEM.ref_trac,  WHERE pricelist.description LIKE '%".$description."%' AND pricelist.ref_trac LIKE '%".$ref_trac."%' AND pricelist.old_ref LIKE '%".$old_ref."%' AND OEM.OEM LIKE '%".$oem."%'";


The original query to option the display results is:

$sql = "SELECT pricelist.description, pricelist.ref_trac, pricelist.old_ref, pricelist.PRICE_ID, pricelist.bruto_prices, OEM.OEM FROM pricelist INNER JOIN OEM ON pricelist.ref_trac = OEM.ref_trac WHERE pricelist.description LIKE '%".$description."%' AND pricelist.ref_trac LIKE '%".$ref_trac."%' AND pricelist.old_ref LIKE '%".$old_ref."%' AND OEM.OEM LIKE '%".$oem."%' ORDER BY ".$sort." DESC LIMIT ".$limits[0].", ".$limits[1]."";

I can also tell that the query is very slow it takes 13 seconds to process :(
I think I'm going the wrong way with this and there might be an easier way (maybe even with tutorial) if so please let me know :)
I hope someone can help me out with this soon I'd be very thankfull for that.

#2 fenway

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

Posted 24 October 2006 - 08:51 PM

Well, the reason it's slow is that you're using LIKE, so no index can ever be utilized.  As for the COUNT, you can also take advantage of CALC_SQL_FOUND_ROWS without have to do an explicit count.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users