I'm trying to make these SQL querys faster. I've tried with indexes and so on but I'm abit unsure how to make it faster. Any tips or points are welcome. Its abit old code that an old programmer made, trying to fix it up. It's a MySQL database.
I have two tabels:
wp_l (550 000rows and growing each day) and wp_lP (540 000rows and growing)
Structure:
wp_l:
id - int(9)
epost - (varchar255)
userID - int(9)
date - timestamp
and a few more fields containing info thats not used in this case.
wp_lP:
id - int(9)
lID - int(9) [is the id it belongs to in wp_l, nothing is done in "mysql" to make them relate]
pID - int(9)
quantity - int(9)
My php code:
<?php
$years = $wpdb->get_results($wpdb->prepare("SELECT YEAR(date) AS year FROM wp_l WHERE userID = %d OR epost = %s GROUP BY year DESC", $current_user->ID, $current_user->user_email));
if(count($years) == 0) {
//No result
} else {
foreach ( $years as $year ) {
echo $year->year;
$myOrders2 = $wpdb->get_results($wpdb->prepare("SELECT * FROM wp_l WHERE YEAR(date) = %s AND (userID = %d OR epost = %s) ORDER by id DESC", $year->year, $current_user->ID, $current_user->user_email), ARRAY_N);
for($i=0; $i<count($myOrders2); $i++) {
$ar = substr($myOrders2[$i][3], 0, 4);
$ordersProducts2[$i] = $wpdb->get_results($wpdb->prepare("SELECT * FROM wp_lP WHERE lID = %d ORDER by id", $myOrders2[$i][0]), ARRAY_N);
for($j=0; $j<count($ordersProducts2[$i]); $j++) {
$loopProductID = $ordersProducts2[$i][$j][2];
$quantity = $ordersProducts2[$i][$j][3];
$tid = $myOrders2[$i][3];
include(locate_template('loopfile.php'));
}
}
}
}
?>
Thanks in advance!