SQL structure

[Michael Harrison]

My host has disabled my account because of long running queries associated with s9y and while attempting to isolate the cause I noticed that the sql admin was complaining about a couple of tables.

With serendipity_entrytags it's saying "PRIMARY and INDEX keys should not both be set for column `entryid`"

The index structure shows as:

Indexes: Documentation Keyname Type Cardinality Field
PRIMARY PRIMARY 393 entryid, tag
tagsentryindex INDEX 131 entryid
tagsTagIndex INDEX 393 tag

There are other fields that the admin complains about as well.

Is this the expected or is this a sign that my database isn't well, possibly from regular upgrading of s9y over the last year?

[garvinhicking]

Hi!

Sadly phpMyAdmin complains about some indices in a wrong way. Actually there are cases where duplicate indexes do make sense. So you shouldn't really care about this one.

Can you get the long running queries from your provider? Then we can look exactly what needs to be tuned. Maybe its just a bad plugin like the Statistics plugin that can cause very long running queries.

Best regards,
Garvin[/img]

[Michael Harrison]

Thanks Garvin.

The last such query was...

| 539 | dragon5_serendip | localhost | dragon5_serendipity | Query | 341 | statistics |SELECT neg.tag AS tag, count(neg.tag) - 26 AS total
FROM serendipity_entrytags AS main
LEFT JOIN serendipity_entrytags AS neg ON main.entryid = neg.entryid LEFT JOIN serendipity_entrytags AS sub0 ON main.entryid = sub0.entryid LEFT JOIN serendipity_entrytags AS sub1 ON main.entryid = sub1.entryid LEFT JOIN serendipity_entrytags AS sub2 ON main.entryid = sub2.entryid LEFT JOIN serendipity_entrytags AS sub3 ON main.entryid = sub3.entryid LEFT JOIN serendipity_entrytags AS sub4 ON main.entryid = sub4.entryid LEFT JOIN serendipity_entrytags AS sub5 ON main.entryid = sub5.entryid LEFT JOIN serendipity_entrytags AS sub6 ON main.entryid = sub6.entryid LEFT JOIN serendipity_entrytags AS sub7 ON main.entryid = sub7.entryid LEFT JOIN serendipity_entrytags AS sub8 ON main.entryid = sub8.entryid LEFT JOIN serendipity_entrytags AS sub9 ON main.entryid = sub9.entryid LEFT JOIN serendipity_entrytags AS sub10 ON main.entryid = sub10.entryid LEFT JOIN serendipity_entrytags AS sub11 ON main.entryid = sub11.entryid LEFT JOIN serendipity_entrytags AS sub12 ON main.entryid = sub12.entryid LEFT JOIN serendipity_entrytags AS sub13 ON main.entryid = sub13.entryid LEFT JOIN serendipity_entrytags AS sub14 ON main.entryid = sub14.entryid LEFT JOIN serendipity_entrytags AS sub15 ON main.entryid = sub15.entryid LEFT JOIN serendipity_entrytags AS sub16 ON main.entryid = sub16.entryid LEFT JOIN serendipity_entrytags AS sub17 ON main.entryid = sub17.entryid LEFT JOIN serendipity_entrytags AS sub18 ON main.entryid = sub18.entryid LEFT JOIN serendipity_entrytags AS sub19 ON main.entryid = sub19.entryid LEFT JOIN serendipity_entrytags AS sub20 ON main.entryid = sub20.entryid LEFT JOIN serendipity_entrytags AS sub21 ON main.entryid = sub21.entryid LEFT JOIN serendipity_entrytags AS sub22 ON main.entryid = sub22.entryid LEFT JOIN serendipity_entrytags AS sub23 ON main.entryid = sub23.entryid LEFT JOIN serendipity_entrytags AS sub24 ON main.entryid = sub24.entryid LEFT JOIN serendipity_entrytags AS sub25 ON main.entryid = sub25.entryid
WHERE (sub0.tag = 'holography' AND sub1.tag = 'pages' AND sub2.tag = 'gallery' AND sub3.tag = 'v' AND sub4.tag = 'family' AND sub5.tag = 'MichaelH' AND sub6.tag = 'gallery' AND sub7.tag = 'v' AND sub8.tag = 'family' AND sub9.tag = 'MichaelH' AND sub10.tag = 'Holograms' AND sub11.tag = 'SandDollarLeft_amp_Right' AND sub12.tag = 'pages' AND sub13.tag = 'contactform' AND sub14.tag = 'gallery' AND sub15.tag = 'v' AND sub16.tag = 'family' AND sub17.tag = 'MichaelH' AND sub18.tag = 'Holograms' AND sub19.tag = 'pages' AND sub20.tag = 'gallery' AND sub21.tag = 'v' AND sub22.tag = 'family' AND sub23.tag = 'MichaelH' AND sub24.tag = 'Holograms' AND sub25.tag = 'SandDollarLeft_amp_Right' )
AND (neg.tag != 'holography' AND neg.tag != 'pages' AND neg.tag != 'gallery' AND neg.tag != 'v' AND neg.tag != 'family' AND neg.tag != 'MichaelH' AND neg.tag != 'gallery' AND neg.tag != 'v' AND neg.tag != 'family' AND neg.tag != 'MichaelH' AND neg.tag != 'Holograms' AND neg.tag != 'SandDollarLeft_amp_Right' AND neg.tag != 'pages' AND neg.tag != 'contactform' AND neg.tag != 'gallery' AND neg.tag != 'v' AND neg.tag != 'family' AND neg.tag != 'MichaelH' AND neg.tag != 'Holograms' AND neg.tag != 'pages' AND neg.tag != 'gallery' AND neg.tag != 'v' AND neg.tag != 'family' AND neg.tag != 'MichaelH' AND neg.tag != 'Holograms' AND neg.tag != 'SandDollarLeft_amp_Right' )
GROUP BY neg.tag |

[garvinhicking]

Hi!

The Freetag is causing this queries. Can it be that you are heavily using it? I've never seen a query where such a long number of leaf node tags was returned.

Sadly this simply is a query that takes time, because for many entries and many tags, a lot of data needs to be computed. The only way I see around that is to either not use tags, use less tags or to disable some tagging features that display leaf node tags or others...

Which version of the freetag plugin are you using?

Maybe if you can execute exactly that query and prefix it with "EXPLAIN" you will get an output of the query and if indices are used, so that we could see if there is something to optimize there?

Regards,
Garvin

[Michael Harrison]

Thanks, I am using a fair number of tags but I wouldn't consider it to be a "lot" but maybe it is...

These are my current tags:

"|2: 2004, 2006, |6: 666, |A: aggressive, align, annoy, attack, automation, |B: baen, batterygeek, battlefield, beast, belize, big brother, biology, blog, blood, bunny, |C: camera, cancer, caribbean, cat, ceili, cell, celtic, charlie brown, chase, china, chip, cleaning, clothing, collodion, comcast, computer, contest, course, credit card, |D: dance, day, dead, death, defect, delivery, denton, destroy, detect, detection, dollar, dolphin, drug, dvorack, |E: ebay, election, entangle, exhibit, explosive, extension, |F: face, film, fish, flag, flask, flight, flook, florida, flow, flying spaghetti monster, fraud, fsm, fun, fund-raiser, |G: game, genetic, glass, gold, gravity, gun, |H: half-life, halloween, harassment, hell, hologram, holography, hotplate, hsl, |I: idiot, impersonation, independence, instruction, intel, interferometer, ireland, irish, |J: jamaica, jim, |L: lame, laser, lawsuit, led, level, lighting, |M: magnetic, medallion, microscope, microscopy, military, mindstorms, mini cooper, missile, moonshine, mount, move, mri, |N: nanoparticle, nanotech, news, nxt, |O: oflaherty, online, open source, optic, overlord, |P: PALM, |P: park, pcg, peanuts, pendulum, phone, physics, piercing, pirate, pistacios, plano, pocket, pointer, police, prediction, processing, programming, projectector, projector, pumpkin, |Q: quantum, |R: rabbit, radiation, rail, recreation, retreat, rights, robot, |S: san diego, sand, satellite, scma, scottish, scream, security, set, set dance, show, simulation, slave, sleep, slingshot, snoopy, space, special, speckle, spectra, speeding, spin, spoof, spotting, st thomas, stem, stir, storage, store, strange, stroke, studio, stupid, sub, |T: telecommunication, teleport, terawatt, terrorism, theft, tlapd, travel, trinity hall, tutorial, tv, |U: ucsb, |U: USA, |V: valve, video, |W: watching, wheaties, woodstock, workshop, |X: x-ray, |Y: youtube,"


I'm using 2.63 of the freetag plugin

[garvinhicking]

Hi!

In your freetag plugin configuration, did you set the use of Meta-Keywords to a value larger than 0? This could help not issuing the query you have problems with.

Also disabling to show the "Tag Cloud" will bypass this massive query.

What about the 'EXPLAIN SELECT ... ' result?

Regards,
Garvin