Fix for more sql speed

Jadmanx · 2014-01-22 10:06:05

hey i what to optimize for more sql speed.

i whant to deactivate the
"SELECT COUNT(*) AS total_videos FROM x_video_category AS c"
and
"UPDATE x_video SET total_views = total_views+1, vie"
i think it count every movie view.

p.s. i alrady have deaktivate "Track Views" in the admin

symtab · 2014-01-22 11:38:04

In which file is the first query? Second one you can comment out with // in modules/video/components/view.php (however this query is very very fast and it shouldnt be a speed problem).

Jadmanx · 2014-01-22 12:02:05

i wont know where the codes are. i only see thats the most sql process that slow down my server. i see it in the MySQL Process List.
i downt need to count the movies!

downt know what i have to // in modules/video/components/view.php
can you help please.

i alrady have deaktivate "Track Views" in the admin but downt worke !?!

Jadmanx · 2014-01-23 10:33:29

no help ???

symtab · 2014-01-23 12:10:56

In modules/video/components/view.php comment out (you use // to comment out) the following line:

            $this->db->query("UPDATE #__video
                              SET total_views = total_views+1,
                                  view_time = ".time()."
                              WHERE video_id = ".$this->video_id." LIMIT 1");
        
            $this->db->query("UPDATE #__user_activity SET total_video_views = total_video_views+1 WHERE user_id = ".$owner_id." LIMIT 1");
$this->db->query("UPDATE #__user_activity SET total_viewed_videos = total_viewed_videos+1 WHERE user_id = ".$user_id." LIMIT 1");

(you can comment out all the above lines i fyou dont want counting, but these queries are very very fast).

The category query is required for the category pages.

Nuevolab · 2014-01-23 15:57:26

Indeed update views or total user videos are not slow queries.
But COUNT queries belong to those slowest.

I've seen COUNT is used for comments and related videos.
For related I'm not sure if it so necessary....
For comments, if pagination used it's must. But if pagination ajaxed, there's no such need as well.

But whatever, if mysql cached it shouldn't slow down mysql even with COUNT queries.

I've seen sites using several of COUNT queries for on page statistics.
Without cache and with big videos table they were slowing down mysql and page load speed a lot.

Same on other script for categories page there was count query for total videos in each category.
With big number of categories and big videos table such page was loading ages on some servers.
This was pretty stupid idea to use COUNT query only to show number of videos on template, who cares about it...

No doubt, COUNT queries are KILLERS for mysql.
But it's not that simple to replace it with something else in some situations.

Last edited by Nuevolab (2014-01-23 16:03:50)

Jadmanx · 2014-01-24 00:48:24

now its real faster !!!

NO queries are faster than only fast queries *lol

symtab · 2014-01-24 11:32:40

The COUNT(*) are very very fast if you use indexes with MyISAM (myisam has a built in feature for this). With InnoDB the COUNT(*) queries are slow (but this can be bypassed with caching) even with indexes.

Jadmanx · 2015-06-22 23:03:14

can you tell me how i can full deactivate the

UPDATE ca_video SET total_views = total_views+1, vie

and

SELECT COUNT(*) AS total_videos FROM ca_video_category AS c

in the newest version of AdultScript Pro ? i have edit modules/video/components/view.php but already have this queries.
p.s. i have video views disable in Admin
Thanks

Last edited by Jadmanx (2015-06-22 23:14:22)

Jadmanx · 2015-06-23 23:09:00

no help ?

symtab · 2015-06-24 06:49:13

The Track Views feature in Admin will save all video view information (like ip, view date, user id). This can be useful if you have a premium site and you want to find out if someone is cheating, or if you want to display stats.

Disabling the COUNT(*) will not work. This is required for all video category pages. The COUNT(*) part of the query is optimized, however the other arguments for the videos cannot be optimized (this could work only if we had 1 category / video limit).

Adult Script Pro Community Forums

#1 2014-01-22 10:06:05

Fix for more sql speed

#2 2014-01-22 11:38:04

Re: Fix for more sql speed

#3 2014-01-22 12:02:05

Re: Fix for more sql speed

#4 2014-01-23 10:33:29

Re: Fix for more sql speed

#5 2014-01-23 12:10:56

Re: Fix for more sql speed

#6 2014-01-23 15:57:26

Re: Fix for more sql speed

#7 2014-01-24 00:48:24

Re: Fix for more sql speed

#8 2014-01-24 11:32:40

Re: Fix for more sql speed

#9 2015-06-22 23:03:14

Re: Fix for more sql speed

#10 2015-06-23 23:09:00

Re: Fix for more sql speed

#11 2015-06-24 06:49:13

Re: Fix for more sql speed

Board footer