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

100 Million! thats insane, i'm surprised your host hasn't kicked you out yet. The only thing that comes to mind is the Zend implementation of Lucene. (http://framework.zend.com/manual/en/zend.search.lucene.html) This would allow you to keep the search capabilities but you would be moving the burden to the filesystem. Just an option to look at, but you really need to consider a dedicated host if your requirements are that high. Also I will second the above, what are you doing that has 100 million rows?

Haha yea i'm familiar with that one, i've been using it for migration for a couple years now. But it would be really nice if i could have something that would do more work for me. Something like JAM for java.

I am looking for a utility that controls the migration from development to production (in this case moving from one directory to another). I don't need anything very feature rich, just pick source folder, pick destination folder, and ideally have the ability to exclude certain files from the transfer.

"Table './besterne_besternews/GNN_feeditems' is marked as crashed and last (automatic?) repair failed"

I ran a repair operation that did not finish, the server restarted before completing, and i was wondering if there was some operation i could run (possibly RESET or FLUSH) that would simply return the table to a operationally state. Thanks for the help

The problem with full text is it will return partial matches. For example, "Bill must cross the gates" is not the same as "bill gates", but will be returned in a full text search, not to mention partial results like "bill throws the ball" would cause results also. I can't put the whole search query in quotes, cause i don't want to match the whole thing, just part of it, and I can't think of any way to determine which part should be in quotes, programmatically. And to solve the partial problem, i could put "+" on the terms, but i don't want all the words to be present, just the one that make up the topic name.

I'm trying to pull certain phrases out of search queries that users enter. For example, if the search query is "Bill Gates turns 52", I would like to catch "Bill Gates" which is an entry in the MySql table `topics` under the column `topic`. So how do I preform this type of search on the table.. something like "Search Query" CONTAINS (`topic`). It is not a wildcard search (%Search Query%) because the query is bigger than the value in the DB and full text does not work because I want exact matches to the topic name in the database. Thanks for the help

I'm trying to remove the dangerous site warning when logging into my site using yahoo's openid page, but my xrds document seems to be having no effect. XML Document: <?xml version="1.0" encoding="UTF-8"?> <xrds:XRDS xmlns:xrds="xri://$xrds" xmlns:openid="http://openid.net/xmlns/1.0" xmlns="xri://$xrd*($v*2.0)"> <XRD> <Service priority="1"> <Type>http://specs.openid.net/auth/2.0/return_to</Type> <URI>http://www.besternews.com/openid/</URI> </Service> </XRD> </xrds:XRDS> On the php page http://www.besternews.com/openid/, which is the return_to sent with the openid request, there is an http header (x-xrds-location: http://www.besternews.com/xrds.php) as well has a meta tag equivalent. I'm not sure what else i'm suppose to do but would greatly appreciate anyone's help.

What are the other services on the web that provide a product similar to SitePal or voki? Or what are the best Flash TTS libraries to allow a developer to produce a similar product?

I am using a php script to optimize the CSS and JS of my site on demand. (it is optimized and cached, until the file is modified). I use mod_rewrite to achieve this: RewriteRule ^(.*\.((js)|(css)))$ http://www.mysite.com/loader.php?p=$1 However, with this in place, images inside the CSS scripts are no longer working for CSS scripts not within the www subdomain. I think it is because i am changing subdomains but if i understand mod_rewrite correctly, the accesser should not even know the URL is changing. Any ideas?