Dragonfly CMS v9 ⇒ Modules & Blocks :: Archives ⇒ Advice needed on speeding up a few queries. :: Archived ⇒ Community Forums ⇒ CPG Dragonfly™ CMS
Forum IndexModules & Blocks

Archived ⇒ Advice needed on speeding up a few queries.


I need some slow Query advice. Below are 3 queries that are running real slow compared to the rest. What can I do to make these run faster?

* 0.0565 seconds - LINE 29: SELECT lid from cms_poems_poems WHERE allowcomments != 3 and poemsratingsummary != 0 AND submitter !='$username' and totalvotes >= 6 and NOT EXISTS (SELECT v.ratinglid FROM cms_poems_votedata As v WHERE lid = v.ratinglid AND ratinguser='$username') ORDER BY RAND()

* 0.7001 seconds - LINE 1324: SELECT lid, title, jgroup, description, date, hits, poemsratingsummary, totalvotes, totalcomments, name, cid, allowcomments from cms_poems_poems ORDER BY RAND() LIMIT 1

* 0.2995 seconds - LINE 51: SELECT p.title, p.lid, p.submitter FROM cms_poems_poems as p where submitter !='$username' and p.allowcomments != 3 AND NOT EXISTS (SELECT v.ratinglid FROM cms_poems_votedata As v WHERE p.lid = v.ratinglid AND ratinguser='$username') ORDER BY RAND() LIMIT 1

----------

table: cms_poems_poems - Rowcount 10,821
lid int(11)
cid int(11)
sid int(11)
title varchar(100)
jgroup varchar(100)
description text
date date
name varchar(100)
email varchar(100)
hits int(11)
submitter varchar(60)
poemsratingsummary double(6,4)
totalvotes int(11)
totalcomments int(11)
allowcomments int(11)
slam_wins tinyint(4)

indexes:
primary - lid
index - sid
index - cid
index - date, jgroup
index - hits, jgroup
index - name

----------------

table: cms_poems_votedata - Rowcount 26,546
ratingdbid int(11)
ratinglid int(11)
ratinguser varchar(60)
rating int(11)
ratinghostname varchar(60)
ratingcomments text
ratingtimestamp datetime
favorite_poem tinyint(4)
anonymous tinyint(4)

indexes
primary - ratingdbid
index - ratinguser, ratinglid
index - ratinglid, favorite_poem
index - ratinglid, ratingtimestamp
index - ratinglid, ratinguser

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Unix / 2.0.46 (Red Hat) / 0.9.7a / 4.1.9-standard / 4.3.2 / 9.0.6.1


I'm no SQL expert - way too rusty - but do you really need 3 separate queries? Can you merge them into 1 single SQL query, and store the result? If you need 3 results, just change Limit 1 to Limit 3?

That would shave at least a 3rd of the total query time I would think.

NOT EXISTS should be quicker than the comparable Join, but you could always try it as a Join, and time the differences.

Pro_News CM™ - Content Management for Dragonfly CMS™

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux / 1.3.39 - 2.4.9 / 5.5.42 - 5.6.16 / 5.4.37 - 5.5.11 / 9.4


Oh, the 3 queries are from 2 separate modules and 1 block. I just typed everything out that way to make it easier to present.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Unix / 2.0.46 (Red Hat) / 0.9.7a / 4.1.9-standard / 4.3.2 / 9.0.6.1


The ORDER BY RAND() is going to be the slow part. Consider looking at one of the Coppermine random blocks and see how it's done there. It just gets MySQL to select a whole bunch of lines, then has PHP randomize them instead of MySQL.

Diagon Alley - Top Design

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Linux/1.3.37/4.1.21-standard/4.4.4/9.1.1


What I did instead of using Order by RAND, is I took a rowcount of the table and then took a random number from 1 to rowcount-1. I then select >= the row's unqiueID based on that random number.

In one case if I don't find a match because I've already voted on that row, I just display a message.

The 0.7001 query went down to .0005

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Unix / 2.0.46 (Red Hat) / 0.9.7a / 4.1.9-standard / 4.3.2 / 9.0.6.1

All times are UTC