Jump to content

Archived

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

vexon

count an inner join query

Recommended Posts

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.

Share this post


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

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.