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:
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:
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:
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.
Offline
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:
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:
WHERE is_videojs = 'true'
Offline