I also started going through my emails, I have a big backlog due to not being able to check them while away.
Billy had finished the Communist Halloween video while we were away, so a good job it was finished in time for Halloween:
My boring life
Karl Marx and hands for the different models that didn't have hands made yet |
The completed crew - Karl Marx tramp body, Karl Marx, Donald Rumsfeld, Lenin, Chairman Mao, Maggie Thatcher, Rupert Murdoch, Ronald McDonald, Sir Fred Goodwin, Adam Smith |
However, even after adding these sites to the trusted sites list, and restarting the machine, I still got the same message. It seems a bit strange that microsoft have decided to make it difficult to check for updates - I'd have thought they would want people to keep windows up to date.
To continue, you must first add this website to your trusted sites in Internet Explorer.
The site cannot determine which updates apply to your computer or display those updates unless you change your security settings to allow ActiveX controls and active scripting. The best way to do this without lowering your security settings is to make this site a trusted website. Your security settings will continue to block potentially harmful ActiveX controls and scripting from other sites but you will be able to get updates.
To make this site a trusted website:
Note: The asterisks and different addresses allow your computer to work with the site, no matter how you try to access it from your computer or the Web.
- In Internet Explorer, click Tools, and then click Internet Options.
- On the Security tab, click the Trusted Sites icon.
- Click Sites and under Add this website to the zone, copy and paste these website addresses. You can only add one address at a time and you must click Add after each one. Note that you may need to uncheck "Require server verification (https:) for all sites in this zone."
- http://update.microsoft.com
- https://update.microsoft.com
- http://*.update.microsoft.com
- https://*.update.microsoft.com
- http://download.windowsupdate.com
<summary>A picture of my new car</summary>
<content src="/mypng2.png" type="image/png" />
<category term="photography"/>
<category term="road" scheme="http://www.flickr.com/photos/tags/" />
<category><![CDATA[Photography]]></category>
<category scheme="http://edward.oconnor.cx/tags/" term="foo" label="Foo" />
But I don't currently have any way for people to view a list of tags or photos with a certain tag on my website. So although I could use the above representation, the scheme URL would either lead to a 404, or I could put up a blank page there, which would be just as useful.
So I think what I am going to do for the moment is use RedBubble's method. Then if or when I add the ability to browse photos by tags, I can add in the scheme url, ala Flickr and Edward O’Connor's suggestion 1.
Now I knew how I wanted the tags formatted, I just had to look at how to extract them from the database and format them appropriately. With MySQL you can only have scalar values, so you can't select each image record with the keywords as an array. Instead you can:
$result = query('SELECT img_keywords.img_id, keywords.Subject
FROM keywords
LEFT JOIN img_keywords ON keywords.id = img_keywords.keywords_id
WHERE img_keywords.img_id IN (list of ids you want records for or a subquery to select records here)'
while($row = $result->fetch_assoc()){
$images[$img_id]['keywords'][] = $row['Subject'];
}
There may be other methods you could use as well, but I think in theory that the last option above should be the most efficient, though I don't really like it as it is mixing data with presentation/markup.
Anyway, when I added the code for that last method to my query, the query was taking about 1s!!! So I spent quite a while trying to find out what was wrong. I looked at suggested keyword table structures and indexes, but it was actually the way my query was structured that was slowing it down.
This was the original query (before adding in anything to do with keywords / tabs, this took 0.056s to run:
SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name,
SUBSTRING_INDEX(imageData.ImageDescription, '\n', 1) AS summary, imageData.GPSLatitude, imageData.GPSLongitude, imageData.GPSAltitude, DATE_FORMAT(imageData.Last_updated,'%Y-%m-%dT%TZ') AS Last_updated
FROM images
LEFT JOIN imageData ON images.id = imageData.id
LEFT JOIN img_categories ON img_categories.img_id = images.id
LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = 14
GROUP BY images.id
ORDER BY images.id DESC
LIMIT 0,25
By moving the part of the query that selects all matching image ids into a subquery, the time was dramatically cut to around 0.0025s.
SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name,
SUBSTRING_INDEX(imageData.ImageDescription, '\n', 1) AS summary, imageData.GPSLatitude, imageData.GPSLongitude, imageData.GPSAltitude, DATE_FORMAT(imageData.Last_updated,'%Y-%m-%dT%TZ') AS Last_updated
FROM images
LEFT JOIN imageData ON images.id = imageData.id
WHERE images.id IN(SELECT DISTINCT img_categories.img_id
FROM img_categories
LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = 14)
GROUP BY images.id
ORDER BY images.id DESC
LIMIT 0,25
Finally, I also tried using the subquery just to select the category ids we wanted to show images for, and then selecting the image ids that match these in the main query, but this didn't seem to have any benefit over the above, with the run time still around 0.0025s.
SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name,
SUBSTRING_INDEX(imageData.ImageDescription, '\n', 1) AS summary, imageData.GPSLatitude, imageData.GPSLongitude, imageData.GPSAltitude, DATE_FORMAT(imageData.Last_updated,'%Y-%m-%dT%TZ') AS Last_updated
FROM images
LEFT JOIN imageData ON images.id = imageData.id
LEFT JOIN img_categories ON img_categories.img_id = images.id
WHERE img_categories.categories_id IN(SELECT DISTINCT node.id
FROM categories AS node,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = 14)
GROUP BY images.id
ORDER BY images.id DESC
LIMIT 0,25
Now when I add in the code to get the keywords, the query is much faster than the 1s it was taking previously - 0.0035s, a massive difference from a simple change.
#0.0035SELECT images.id, images.Headline, images.filename, images.Rating, images.url_name,
SUBSTRING_INDEX(imageData.ImageDescription, '\n', 1) AS summary, imageData.GPSLatitude, imageData.GPSLongitude, imageData.GPSAltitude, DATE_FORMAT(imageData.Last_updated,'%Y-%m-%dT%TZ') AS Last_updated,
CONCAT(' \n ') AS tags
FROM images
LEFT JOIN imageData ON images.id = imageData.id
LEFT JOIN img_keywords ON images.id = img_keywords.img_id
LEFT JOIN keywords ON keywords.id = img_keywords.keywords_id
WHERE images.id IN(SELECT DISTINCT img_categories.img_id
FROM img_categories
LEFT JOIN categories AS node ON node.id = img_categories.categories_id,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.id = 14)
GROUP BY images.id
ORDER BY images.id DESC
LIMIT 0,25
If you're good with MySQL, then the above may come as no surprise, but I had been under the general impression that it's more efficient to keep everything in one query than to have to use separate queries or subqueries. I will probably see if there's anything else I can do to improve it a bit more tomorrow.