Post by Joe1234 » Fri Jan 20, 2023 8:19 am

I came up with this thing but I need it to run faster. Can this be improved for a faster response time?

Code: Select all

				//QUery the size items
				$sqlquery = $this->db->query("SELECT pa.product_id, q.name, p.model, p.image, p.price, p.date_added FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description q ON (p.product_id = q.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pa.attribute_id = '36' AND text = '" . $resultSIZE[0]['text'] . "' AND (p.status = 1 AND p.quantity > 0)");//PRODUCT_ID");
				$resultSIZE = $sqlquery->rows;
				
				//Query the gender items
				$sqlquery = $this->db->query("SELECT pa.product_id, q.name, p.model, p.image, p.price, p.date_added FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description q ON (p.product_id = q.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pa.attribute_id = '35' AND text = '" . $resultGENDER[0]['text'] . "' AND (p.status = 1 AND p.quantity > 0)");//PRODUCT_ID");
				$resultGENDER = $sqlquery->rows;

				//Compare the arrays and return the elements that are in each array
				$new_products = array_intersect_key( $resultSIZE, array_unique( array_map('serialize' , $resultGENDER ) ) );

				//Sort the array based on date added
				array_multisort(array_column($new_products, 'date_added'), SORT_ASC, $new_products);

				//Return the first three items
				$new_products = array_slice($new_products, 0, 3);
Last edited by Joe1234 on Wed Jan 25, 2023 2:02 am, edited 1 time in total.

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by paulfeakins » Fri Jan 20, 2023 7:16 pm

Well I asked ChatGPT and it said:
ChatGPT wrote:
There are a few ways to potentially improve the response time of this code.

1. Indexing: Make sure that the 'product_id', 'attribute_id', and 'text' columns in the 'product_attribute' table are indexed. This will allow the database to quickly find the matching rows for the queries.

2. Reduce the number of rows returned: The SELECT statements are returning all columns of all the matching rows. If you only need a subset of the columns, you can specify only those columns in the SELECT statement which will reduce the amount of data that needs to be returned.

3. Caching: You can cache the result of the queries in memory so that you don't have to query the database every time the same query is executed.

4. Optimize the array operations: It might be helpful to use more efficient array operations such as array_uintersect() and array_column() which are more optimized for large arrays.

5. Use JOIN instead of two separate query to get the data in one query. This can be a good solution if you want to reduce the number of queries to the database.

Keep in mind that these suggestions may or may not work for your specific use case, and that the best way to optimize your code is to profile it and identify the specific areas that are causing slowdowns.
They seem like pretty good starting points.

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by Joe1234 » Fri Jan 20, 2023 8:18 pm

1. They are
2. THIS is the main thing I'm looking for assistance with. I've tried for a couple hours and wasn't ending up with the expected results, which is why I have this long workaround.
3. I didn't know about this. I will look into getting one of these extensions. Thanks.
4. THIS is why I posted....
5. I don't really see how to do that here.

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by SohBH » Sat Jan 21, 2023 1:01 am

Code: Select all

public function get3SizeGender(): array {
$sql = "SELECT pa.product_id, q.name, p.model, p.image, p.price, p.date_added FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description q ON (p.product_id = q.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pa.attribute_id = '36' AND text = '" . $resultSIZE[0]['text'] . "' AND (p.status = 1 AND p.quantity > 0)
UNION
SELECT pa.product_id, q.name, p.model, p.image, p.price, p.date_added FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description q ON (p.product_id = q.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pa.attribute_id = '35' AND text = '" . $resultGENDER[0]['text'] . "' AND (p.status = 1 AND p.quantity > 0)
ORDER BY p.date_added LIMIT 3";
$query = $this->db->query($sql);
return $query->rows;
}

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by Joe1234 » Sun Jan 22, 2023 9:05 am

Thanks, but that query is throwing an error and I don't know exactly how to fix it.

Code: Select all

Fatal error:  Uncaught Exception: Error: Table 'p' from one of the SELECTs cannot be used in field list<br />Error No: 1250

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by SohBH » Sun Jan 22, 2023 2:53 pm

Change
ORDER BY p.date_added
to
ORDER BY date_added

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by Joe1234 » Wed Jan 25, 2023 1:58 am

Something is off between what this is producing and the full code that I have, I don't know what it is right now I'll have to tinker with it, but for the most part it seems to be working now without an error. Thanks.

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by Hourglasss » Wed Jan 25, 2023 5:43 am

https://nitropack.io/ they have opencart extension works great

Active Member

Posts

Joined
Mon Jan 23, 2023 10:39 pm
Who is online

Users browsing this forum: Google [Bot] and 87 guests