5 replies to this topic

[lol33d]

Hi guys

my query is very slow :(

please check my query, and create better:

			$result3=mysql_query("SELECT * FROM myhdc WHERE `supp` = 'LD' AND `part` = 'YES' AND `fname` <> '0' AND `partfix` = 'NO' GROUP BY `fname`, `oPRC`, `username`");

			if(mysql_num_rows($result3) > 0)

			{

				while ($rowx=mysql_fetch_assoc($result3))

				{

					$idMIX = (int)$rowx["fname"];


					$result4=mysql_query("SELECT * FROM myhdc WHERE `part` = 'YES' AND `fname` = '" . $idMIX . "' AND `partfix` = 'NO'");

					$num = mysql_num_rows($result4);

					if($num > 0)

					{

						$result5=mysql_query("SELECT * FROM myhdc WHERE `supp` = 'LD' AND `part` = 'YES' AND `fname` = '" . $idMIX . "' AND `partok` = 'YES' AND `partfix` = 'NO'");

						if(mysql_num_rows($result5) == $num)

						{

							$rxxc = 1;

							while ($rx=mysql_fetch_assoc($result5))

							{

								$rxxc *= $rx["zarib"];

							}


							echo $rxxc;

						}

					}

				}

			}

thank you

[Vaielab]

1. Can you show us the "SHOW CREATE TABLE" for your table?

2. You should never use "select *" name your columns it's faster.

3. On your first line add an $i = 0;
In your last while, add an $i++;
and tell us how much $i is at the end of the execution

4. Do you have any index?

[lol33d]

1) no
2) thanks
3) 28218041 records
4) no

help plz

[Orjan]

28 million records? oh.. that's quite a lot. your table really needs an index or two...

[Vaielab]

with 28 218 041 records even if you optimize everything, it will still be a slow query... you should think to remodel your database... and without a "SHOW CREATE TABLE" we can't help you with this

With your code now, you multiply 2 variable 28 218 041 time, even if your sql is optimised, your php will still be slow

[WingedPanther]

It looks like you are requerying the same table in a loop, sometimes with similar conditions. I think you really need to go back and think about what you're trying to accomplish. You should be able to do all of this with a single query, which will vastly speed up your results.