Very slow site load

Question

I made a PHP script which requests information from a MySQL table. If it's correct, it does something, but this script is very slow because there are many rows where it is not correct. Here is the script:

$everypost = mysql_query("SELECT MAX(asd) FROM asd;");
$everypost = mysql_fetch_array($everypost);
$id = $everypost[0];
while ($id > 0 && $i < $var2) {
    $id = $id - 1;
    $tulajid = $context['user']['id'];
    $posts = mysql_query("SELECT * FROM asd WHERE asd='$id' AND owner='$var' GROUP BY(asd);");
    $prow = mysql_fetch_array($posts);
    if(isset($prow['asd'])) {//If it's correct, so the user is the owner
        $i++;
    }
}

My problem is that it checks every post and not only the user's posts. Is there some solution to make it faster? If I remove AND owner ='$var' from the $posts query, it is very fast.

CREATE TABLE `asd` ( 
  `asd` int(10) unsigned NOT NULL, 
  `id_member` mediumint(8) unsigned NOT NULL, 
  `score` smallint(2) NOT NULL, 
  `owner` mediumint(8) unsigned NOT NULL, 
  `log_time` int(10) unsigned NOT NULL, 
  PRIMARY KEY (`id_msg`,`id_member`), 
  KEY id_member (`id_member`), 
  KEY owner (`owner`), 
  KEY owner_2 (`owner`) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

SOLUTED

Your `CREATE TABLE` statement indicates that the `asd` table doesn't even *have* an `owner` column ... — , Mar 03 '13 at 14:57

score 2 · Answer 1 · edited May 25 '19 at 14:28

2

That means you need to review your database design. You probably don't have, and thus need, and index on your owner column.

FYI, you shouldn't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 14:28

Zoe

27,060
21
118
148

answered Mar 02 '13 at 18:21

John Conde

217,595
99
455
496

It is still very slow. – user2127243 Mar 02 '13 at 18:26

prodigitalson · Answer 2 · 2013-03-02T18:34:03.610

0

I think the main problem here isnt necessarily the DB design, but the way you are going about querying the information. You pulling the most recent something in one query, then you are looping down and issuing a query for each iteration of the loop. That sir, is insane. You can do this in way fewer queries by using the correct conditions.

Without more information from your schema i woudl assume you could do something like:

SELECT a.* FROM asd a WHERE a.owner = ? ORDER BY a.asd DESC

That would get you all instances of an asd tuple where the owner is a certain value (you would replace the ? with your value), ordered by the asd, highest to lowest. Im not sure if thats what you want, and if you can post more information about the table and the variables in the PHP i can probably give you a better answer, but regardless of the data youre trying to get im pretty sure there is a much more efficient way of doing it than what you are doing now.

edited Mar 02 '13 at 18:34

answered Mar 02 '13 at 18:25

prodigitalson

60,050
10
100
114

1

So, how should I do it? – user2127243 Mar 02 '13 at 18:27
I was jsut about to get to that... what are `$var` and `$var2` and can you show us the schema of the `asd` table? – prodigitalson Mar 02 '13 at 18:28
$var is the owner of the thing. $var2 is the maximum amount of thing which are showed on one page. ( This is not the full code. ) The asd table: id_msg (=asd) | id_member | score | id_poster (=owner) | log_time – user2127243 Mar 02 '13 at 18:47
And is that he same as `$context['user']['id']` or a diffrent user? and what is `var2`? Can you post the schema for the table, just run a `SHOW CREATE TABLE asd` and post it in your initial question. – prodigitalson Mar 02 '13 at 18:49
CREATE TABLE `asd` ( `id_msg` int(10) unsigned NOT NULL, `id_member` mediumint(8) unsigned NOT NULL, `score` smallint(2) NOT NULL, `id_poster` mediumint(8) unsigned NOT NULL, `log_time` int(10) unsigned NOT NULL, PRIMARY KEY (`id_msg`,`id_member`), KEY `id_member` (`id_member`), KEY `id_poster` (`id_poster`), KEY `id_poster_2` (`id_poster`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And $var = $context['user']['id'] – user2127243 Mar 02 '13 at 18:51
ok so i dont see a column called `asd` in the `asd` table... whats that all about? – prodigitalson Mar 02 '13 at 18:55
I edited my 3. comment to this post, I think you didn't see it, sorry: "$var is the owner of the thing. $var2 is the maximum amount of thing which are showed on one page. ( This is not the full code. ) The asd table: id_msg (=asd) | id_member | score | id_poster (=owner) | log_time" – user2127243 Mar 02 '13 at 18:58

score 0 · Accepted Answer · answered Mar 03 '13 at 14:24

0

It is still slow. The id_msg = asd and the id_poster = owner.

answered Mar 03 '13 at 14:24

user2127243

11
4

Very slow site load

3 Answers3