Piwigo.org

xbgmsharp · 2015-06-25 16:54:49

Hi,

I am planing to use the MySQL View in my plugins, http://www.techotopia.com/index.php/An_ … ySQL_Views
Does anyone as experience with it?
My goal is to reduce the queries by making SQL queries directly to the view.

For example, to list videos today:

Code:

 
SELECT * FROM `piwigo27_images` WHERE ((lcase(`piwigo27_images`.`file`) like '%.ogg') OR (lcase(`piwigo27_images`.`file`) like '%.ogv') OR (lcase(`piwigo27_images`.`file`) like '%.mp4') OR (lcase(`piwigo27_images`.`file`) like '%.m4v') OR (lcase(`piwigo27_images`.`file`) like '%.webm') OR (lcase(`piwigo27_images`.`file`) like '%.webmv')) AND `id`=X;

Query took 0.0010 seconds

By making a view:

Code:

CREATE  VIEW `Videos` AS SELECT * FROM `piwigo27_images` WHERE ((lcase(`piwigo27_images`.`file`) like '%.ogg') or (lcase(`piwigo27_images`.`file`) like '%.ogv') or (lcase(`piwigo27_images`.`file`) like '%.mp4') or (lcase(`piwigo27_images`.`file`) like '%.m4v') or (lcase(`piwigo27_images`.`file`) like '%.webm') or (lcase(`piwigo27_images`.`file`) like '%.webmv'));

Then query the view:

Code:

 
SELECT * from `Videos` WHERE `id`=X;

On the first query it took 0.0010 seconds
On the second query it took 0.0005 seconds

The speed improvement is only on my dev server.

The same could apply for the `latitude` and `longitude` columns.

plg · 2015-10-08 12:09:39

Hi xbgmsharp,

I don't like to use views so I recommend you don't use them :-)

On your example, you improve from 1ms to 0.5ms. That is totally useless in my opinion. 1ms is already perfect.

If you want to avoid to write things like:

Code:

WHERE ((lcase(`piwigo27_images`.`file`) like '%.ogg') OR (lcase(`piwigo27_images`.`file`) like '%.ogv')

... which is ugly I agree, you could have a images.is_videojs (enum true/false) column that you set at the appropriate time. It would be some kind of a cache to avoid the ugly SQL query and have instead:

Code:

WHERE is_videojs = 'true'

Piwigo.org

Announcement

#1 2015-06-25 16:54:49

MySQL View

Code:

Code:

Code:

#2 2015-10-08 12:09:39

Re: MySQL View

Code:

Code:

Board footer