meman1188

This is what I got from an inquiry into the physical setup of the mysql container: The MySQL containers are a dedicated VM running with other instances in a larger database server. Each of the instances has their own dedicated resources assigned to it. The instances would share disk I/O since they are on a shared physical server. These database servers are not a local part of the server and are attached through a network connection. Sounds like to me that it would be unlikely that it would be disk spin up or connection because there are probably other mysql instances reading/writing to drives almost constantly. Only thing would be if my mysql db was stored on a different NAS drive.

I'm running it on a mysql container at media temple using their grid service. This is a description of the container: A dedicated MySQL Server with isolated resources and (mt) optimized configuration. Full insight and customization with built-in reporting and control panel tools. Works seamlessly with existing web sites on the (gs). I'm pretty sure its not actually a dedicated box, but actually a dedicate VM running with several other instances. It's possible that this is the first non-cached call on the page. Everything before this call is a SELECT that could be in the cache. I would say this is unlikely but possible. Is there anything I can do about it (besides just get more hits haha)? Or test to see if it is in fact disc IO.

SQL query: EXPLAIN SELECT views FROM `feeditems` WHERE ITEMID = 3281268; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE feeditems const PRIMARY PRIMARY 3 const 1

It's a simple enough query that I don't think there is much improvement to be made on that (although I did take off the low_priority flag). I'm mostly curious if there is anything I can do in the mysql configuration or the table configuration to improve this query. It seems like a pretty straight forward thing to me that shouldn't be taking 5 seconds to complete. It is a shared host but I have a dedicated resources for MySQL so it shouldn't be affected as much by others. Thanks for the responses!

yea, the low_priority modifier is a recent edition i made to it and it really had no effect at all... I want what the low_priority does, but I don't want mysql to wait for the response. I guess that would be the optimal situation; I send this query to some cue but the script continues on (even finishes) before it is completed. Is this possible?

I have a field that counts the number of times a page is viewed. So when the page is loaded i have a script that does `views` = `views` + 1. Here is an example query: UPDATE LOW_PRIORITY table SET `views` = `views`+1 WHERE ID = 123 views is not an indexed field so there should be no updating of indexes at all. Simply a primary key update (ID is the primary key). Sometimes when I run it in phpMyAdmin it takes subsecond (like .0008sec), sometimes it takes over 5 seconds. Can anyone help me optimize this so that it does not take that long? Thanks so much! It's really drag when the page takes 5 seconds to load when its just waiting on an update.

The reason I have gone with text is because these three fields can be very large, the following are the max_lengths for the fields: title: 442, tags: 2393, desc: 53497 Although the avg_lengths are much more accurate to an average entry: title: 48, tags: 72, desc: 409 The fields used in the join to feeds are already indexed. I'm worried about the FULLTEXT search. I realize its an expensive query to run, but I was wondering what I can do to improve it. It currently averages about 5 seconds.

Thanks for the response! This is a sample query I'm running that lead to the Explain above: SELECT `feeditems`.`ITEMID`, (MATCH(`feeditems`.`title`,`feeditems`.`tags`) AGAINST ('XXX' IN BOOLEAN MODE)*.XXX + MATCH(`feeditems`.`title`,`feeditems`.`tags`,`feeditems`.`desc`) AGAINST ('XXX' IN BOOLEAN MODE)*.XXX + `feeditems`.`pubdate`/XXX) AS `relevance` FROM `feeditems` INNER JOIN `feeds` ON (`feeds`.`FEEDID` = `feeditems`.`feedID` AND `feeds`.`cat` != XXX AND `feeds`.`cat` != XXX) WHERE (MATCH(`feeditems`.`title`,`feeditems`.`tags`) AGAINST ('XXX' IN BOOLEAN MODE)) AND (MATCH(`feeditems`.`title`,`feeditems`.`tags`,`feeditems`.`desc`) AGAINST ('XXX' IN BOOLEAN MODE)) AND (`feeditems`.`ITEMID` != 'XXX') GROUP BY `feeditems`.`title` ORDER BY `relevance` DESC LIMIT XXX; Structure: title, tags, desc: text pubdate: uint(10) The FULLTEXT key is on: title, tags, desc in that order

I was wondering what this means and if it was bad. The index is a fulltext search and the query is running reliatively slow so i was wondering how i'm investigating ways to improve it but have sort of limited knowledge of keys and mysql in general. Thanks for the help This is the explains: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE feeditems fulltext PRIMARY,feedID_pubdate,title title 0 [/td] [td]1 Using where; Using temporary; Using filesort 1 SIMPLE feeds eq_ref PRIMARY,cat_subcat,cat_FEEDID PRIMARY 2 feeditems.feedID 1 Using where

Works great, thanks for the help!

Can you give an example of how I can set that up, or point me in the direction of a tutorial. A quick google search didn't return anything promising.

I would actually like to avoid the subquery if i can, but is there a way to have mysql create the derived table automatically (and update it), or is that something i would need to maintain separately from the original `update` table on the PHP side? Also that gave me the idea of using views. I'm kind of unfamiliar with the specifics, but they seem like something that might be able to do this. Is that correct? Thanks for the response

This is a follow up to a previous post but involves a somewhat different mysql question. I am using a subquery to pull in a value from another table, and if it exists I want to sort using that value, if not, i want to sort using a value in the original table. I realize this is confusing, so I think this is the best way to explain it: SELECT `items` . *, ( SELECT `time` FROM `updates` WHERE `updates`.`id` = `items`.`id` ORDER BY `time` DESC LIMIT 1 ) AS `title_time`, IF(`title_time` IS NOT NULL, `title_time`, `items`.`original`) as `sort_date` FROM `items` WHERE ( `items`.`group_id` IN ( 111, 112) ) ORDER BY `sort_date` DESC LIMIT 9 Basically it is saying `title_time` is not defined, which i understand because the subquery needs to run on each row, but before that can happen it needs to sort the rows, using a value it hasn't pulled yet. I'm just not sure how i can restructure this to accomplish what i'm looking for. Note: there can be more than one row in update per item.id, but i always just want the latest one. So i think this rules out using a join. Thanks for the help!

I am running the query to get data out of table A (items). If there is a corresponding row present in table B (redirects) then I want to sort off of A.`update` otherwise i want to sort off of A.`original`. Table B is accessed by key ex (A.`key` = B.`key`). So this is sort of the sample i have right now but is not working: SELECT `items`.* , CASE WHEN EXISTS ( SELECT 1 FROM `redirects` WHERE `redirects`.`key` = `items`.`key` ) THEN `items`.`update` ELSE `items`.`original` END CASE AS `sort_by` FROM `items` WHERE (`items`.`key` = xxx) ORDER BY `sort_by`DESC Thanks for the help!

I'm looking to do some text to speech with a talking head like SitePal. This is the best I found: http://www.digitalcuriosity.com/VocaliseTTS/VocaliseTTS.htm but it has not been updated in several years and i'm having trouble getting the demo to work in Flash CS3. I don't care if I use flash or silverlight or any other client side solution, but would like something that I control (no monthly contract to 3rd party). Does anyone have any experience with anything like this? Thanks