Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
Ubuntu Jammy
Description
Query using SphinxSE.
When using 10.10.1 or 10.9.2 and earlier.
The queries are super quick.
30 rows in set (1 min 32.022 sec)
After upgrading 10.10.2, and 10.9.3 the same query
30 rows in set (0.125 sec)
When running explain on the query type=eq_ref on the quick version.
and eq|filter on the slow one.
Disabling eq|filter with
set @@optimizer_switch="rowid_filter=off";
Resolved the issue.
Attachments
Issue Links
- is part of
-
-
- Closed
-
Activity
Dave,
You have this problem after upgrading from what version to what version? From 10.9.2 to 10.9.3?
Dave,
Could you please provide the output of EXPLAIN EXTENDED for your query with:
set optimizer_switch='rowid_filter=on'
and
set optimizer_switch='rowid_filter=off'?
Dave,
Where are the definitions of the tables
`usergroup_media`,
channels
?
CREATE TABLE `usergroup_media` ( |
`gid` int(11) unsigned NOT NULL, |
`mid` int(11) unsigned NOT NULL, |
`position` int(4) DEFAULT 0, |
`vhost` int(10) unsigned NOT NULL, |
`treeleft` int(10) unsigned NOT NULL DEFAULT 0, |
PRIMARY KEY (`gid`,`mid`), |
KEY `media_group` (`mid`,`gid`), |
KEY `vhosttree` (`vhost`,`treeleft`,`mid`), |
KEY `gidvhost` (`gid`,`vhost`), |
KEY `vmid` (`vhost`,`mid`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
Igor, yes 10.9.2 to 10.9.3.
So then downgraded to 10.8 to resolve the isssue.
Then
And then again when I went to 10.10 from 10.8
explain extended SELECT `data_mediagallery`.`channel` AS `channel`, `data_mediagallery`.`commentcount` AS commentcount, `data_mediagallery`.`filetype` AS `filetype`, `data_mediagallery`.`hits` AS `hits`, `data_mediagallery`.`id` AS `id`, `data_mediagallery`.`metadata` AS `metadata`, `data_mediagallery`.`title` AS `title`, `data_mediagallery`.`vhost` AS `vhost`, `data_mediagallery`.`votecount` AS `votecount` FROM data_mediagallery INNER JOIN `usergroup_media` ON `data_mediagallery`.`id` = `usergroup_media`.`mid` AND `usergroup_media`.`vhost` = `data_mediagallery`.`vhost` AND `usergroup_media`.`gid` = '422841' INNER JOIN channels ON channels.id = data_mediagallery.channel and channels.treeleft>=301 AND channels.treeright<=388 INNER JOIN sphinxmedia ON sphinxmedia.id = data_mediagallery.id WHERE ( data_mediagallery.hidden = 0 ) AND (1=1) AND (data_mediagallery.context = 1) AND (data_mediagallery.filetype IN (1)) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.upload > "2022-11-22 00:00:00") AND (data_mediagallery.vhost = 123) AND (sphinxmedia.query='mode=extended2;index=realtime_media_123;;filter=moderationstatus,1;filter=vhost,123;filter=gid,422841;range=channelleft,301,388;filter=status,3;filter=context,1;filter=hidden,0;filter=filetype,1;range=upload,1669093200,2145934800;floatrange=latituderadians,0.742523007306,0.758230970574;floatrange=longituderadians,-1.42732013012,-1.41161216685;sort=extended:upload DESC,upload DESC;limit=50;offset=0;maxmatches=10000;') ORDER BY `data_mediagallery`.`upload` DESC LIMIT 0, 50; |
+------+-------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------------------+------+----------+--------------------------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+-------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------------------+------+----------+--------------------------------------------------------------------+ |
| 1 | SIMPLE | sphinxmedia | ref | query | query | 3074 | const | 3 | 100.00 | Using where with pushed condition; Using temporary; Using filesort | |
| 1 | SIMPLE | data_mediagallery | eq_ref | PRIMARY,upload,vhostupload,vhostmodstatus,complexfiletype,vhostcontext,hockeynews_custom,externalcomments,vhostchannel,vhostparent,uploadfiletype,twnusermedia,mfdefault,vhoststartdate,vhostmodupload,vhostlastupdate | PRIMARY | 4 | sabretooth2.sphinxmedia.id | 1 | 0.00 | Using where | |
| 1 | SIMPLE | channels | eq_ref | PRIMARY,idtltr | PRIMARY | 4 | sabretooth2.data_mediagallery.channel | 1 | 100.00 | Using where | |
| 1 | SIMPLE | usergroup_media | eq_ref | PRIMARY,media_group,vhosttree,gidvhost,vmid | PRIMARY | 8 | const,sabretooth2.data_mediagallery.id | 1 | 99.95 | Using where | |
+------+-------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------------------+------+----------+--------------------------------------------------------------------+ |
On/off are both giving the same results now on 10.10.2.
Dave,
What about the definition for table channels?
Re the slow plan with |filter : how did it look like?