Rectangle 27 4

In MS SQL, you can attach a trigger to a table insert/delete/update event that can fire a stored proc to invoke a web service. If the web service is CF-based, you can, in turn, invoke a messaging service using event gateways. Anything listening to the gateway can be notified to refresh its contents. That said, you'd have to see if MySQL supports triggers and accessing web services via stored procedures. You'd also have to have some sort of component in your web app that's listening to the messaging gateway. It's easy to do in Adobe Flex applications, but I'm not sure if there are comparable components accessible in JavaScript.

While this answer does not come close to directly addressing your question, perhaps it will give you some ideas as to how to solve the problem using db triggers and CF messaging gateways.

Yeah, I saw a bunch of Flex examples, which lead my to this question in the first place, but i like your idea about firing a SP that calls a web service. I'm going to keep searching for the right messaging service though as that is what is vexing me the most at the moment.

mysql - Is there a way to 'listen' for a database event and update a p...

mysql ajax coldfusion real-time
Rectangle 27 4

In MS SQL, you can attach a trigger to a table insert/delete/update event that can fire a stored proc to invoke a web service. If the web service is CF-based, you can, in turn, invoke a messaging service using event gateways. Anything listening to the gateway can be notified to refresh its contents. That said, you'd have to see if MySQL supports triggers and accessing web services via stored procedures. You'd also have to have some sort of component in your web app that's listening to the messaging gateway. It's easy to do in Adobe Flex applications, but I'm not sure if there are comparable components accessible in JavaScript.

While this answer does not come close to directly addressing your question, perhaps it will give you some ideas as to how to solve the problem using db triggers and CF messaging gateways.

Yeah, I saw a bunch of Flex examples, which lead my to this question in the first place, but i like your idea about firing a SP that calls a web service. I'm going to keep searching for the right messaging service though as that is what is vexing me the most at the moment.

mysql - Is there a way to 'listen' for a database event and update a p...

mysql ajax coldfusion real-time
Rectangle 27 0

Your key question was, does UUID_SHORT() create values that are unique within time and space as with UUID(). The short answer is yes, as long as you obey the special conditions MySQL requires.

The long answer is yes, but why would you want to use it? The only apparent downsides to UUID() is its representation is less storage-efficient (generates a 36-character string rather than a 64-bit integer), and can't be used with statement-based replication. But UUID() has the big upside of never having to think about the special conditions MySQL requires for UUID_SHORT(). If you're certain the conditions will never be a problem for you, and you're eager to save all of 224 bits per record, UUID_SHORT()is OK to use. But if you have any concerns about the special conditions, then it's probably best to avoid it.

The degree of concern you would have about the special conditions depends a lot on your operational environment. The requirement to never set the system clock backwards between mysqld restarts is a big concern for me. Servers are often configured to have their clocks auto-synched with some other time source (e.g. ntp in unix, Time Service in Windows), and if this behavior isn't carried out to your expectations, then you may not be able to guarantee that condition is met consistently.

sql - Is Mysql UUID_SHORT() comparable to UUID() - Stack Overflow

mysql sql database guid uuid
Rectangle 27 0

CouchDB trades disk-space for read/write speed. It's very likely a comparable CouchDB database will take up more disk-space than MySQL.

That being said, there are a number of things you can do to conserve disk-space:

  • Database file compression is available, (as you pointed out) but you should probably experiment with the various algorithms to find out what works best in practice.
  • Database compaction periodically throughout your import process, and as part of routine maintenance.
  • Lastly, when writing your views, do not emit the entire document as part of the index. Instead, use the include_docs=true query-param. (see docs for other params)

For each view you write like this, each emit means that the document is being duplicated in your database. Thus, you only need to do this: (most of the time, you don't need that 2nd argument)

function (doc) {
  emit(doc.key);
}

I'm sure there are other things you can do, if I think of more I'll amend this answer. (please comment if you know of anything I missed)

mysql - CouchDB Compression - Stack Overflow

mysql nosql compression couchdb
Rectangle 27 0

atn_...
cty_...
seg_...
s2a_...
attraction
city
segment
seg2atn
..._nid
atn_cty_nid
SELECT atn_nid, atn_select_flag, atn_image_name
  FROM attraction
 WHERE aty_cty_nid = 5
;
SELECT atn_nid, TRUE, atn_image_name
  FROM attraction
 WHERE atn_id IN
        ( SELECT s2a_atn_nid
            FROM seg2atn
           WHERE s2a_seg_nid = 68
             AND s2a_image_select_flag = TRUE
        )
;
SELECT atn_nid, TRUE AS atn_select_flag, atn_image_name
  FROM attraction
 WHERE atn_cty_nid = 5
   AND atn_nid IN
        ( SELECT s2a_atn_nid
            FROM seg2atn
           WHERE s2a_seg_nid = 68
             AND s2a_image_select_flag = TRUE
        )
;
SELECT atn_nid, atn_select_flag, atn_image_name
  FROM attraction
 WHERE aty_cty_nid = 5
UNION
SELECT atn_nid, TRUE, atn_image_name
  FROM attraction
 WHERE atn_id IN
        ( SELECT s2a_atn_nid
            FROM seg2atn
           WHERE s2a_seg_nid = 68
             AND s2a_image_select_flag = TRUE
        )
;

ruakh, This works if you change seg_nid to s2a_seg_nid in the intersection. Thanks!! Unfortunately, being a novice, I just realized that I need the UNION of the two queries preserving the atn_select flag from the first query and the s2a_image_select_flag from the second (in a single column) in the result. Can this be done?

seg_nid
s2a_seg_nid
UNION

INTERSECT in MySQL Using Comparable Columns - Stack Overflow

mysql intersect
Rectangle 27 0

My experience is that the mysql server can easily handle a big request. Several small ones instead result in very poor performance. In comparable situations I find one query is most always better in terms of performance. Get the whole data from database and let php sort out the rest.

However, just fetching the data from db in one query is even better. Are you sure you cant use an appropiate "where" clause ?

php - One big query or several small ones? - Stack Overflow

php mysql sql
Rectangle 27 0

In MS SQL, you can attach a trigger to a table insert/delete/update event that can fire a stored proc to invoke a web service. If the web service is CF-based, you can, in turn, invoke a messaging service using event gateways. Anything listening to the gateway can be notified to refresh its contents. That said, you'd have to see if MySQL supports triggers and accessing web services via stored procedures. You'd also have to have some sort of component in your web app that's listening to the messaging gateway. It's easy to do in Adobe Flex applications, but I'm not sure if there are comparable components accessible in JavaScript.

While this answer does not come close to directly addressing your question, perhaps it will give you some ideas as to how to solve the problem using db triggers and CF messaging gateways.

Yeah, I saw a bunch of Flex examples, which lead my to this question in the first place, but i like your idea about firing a SP that calls a web service. I'm going to keep searching for the right messaging service though as that is what is vexing me the most at the moment.

mysql - Is there a way to 'listen' for a database event and update a p...

mysql ajax coldfusion real-time
Rectangle 27 0

Not exactly an answer to the question because the question already has great answers. This is a side info. Enabling general_log really put a dent on MySQL performance. I left general_log =1 accidentally on a production server and spent hours finding out why performance was not comparable to a similar setup on other servers. Then I found this which explains the impact of enabling general log. http://www.fromdual.com/general_query_log_vs_mysql_performance.

Gist of the story, don't put general_log=1 in the .cnf file. Instead use set global general_log =1 for a brief duration just to log enough to find out what you are trying to find out and then turn it off.

logging - How to enable MySQL Query Log? - Stack Overflow

mysql logging
Rectangle 27 0

MySQL and Postgres are the two most common DB backends used in the Django community and have comparable performance. I would agree that Postgres is more popular in the Django community though I don't have any hard numbers to back that up. I certainly don't mean to pick on MySQL but I would say there are some common pitfalls when using MySQL with Django (or MySQL in general):

  • No transaction support with MyISAM (no longer the default in 5.5.5)

There are some docs on the various features of Django which aren't supported on various DB backends: https://docs.djangoproject.com/en/1.3/ref/databases/.

IMHO PostgreSQL is far more advanced and robust than MySQL, but MySQL is more ubiquitous. Many "cloud" providers have MySQL compatible offers that scale very well.

@MarkLavin thanks for the list of unsupported features, I had no idea about some of these limitations. This makes the answer much clearer for me than just comparing MySQL vs PostgreSQL.

No problem. Some of them are kind of minor but when you run into them they can be quite surprising or frustrating.

python - Which database engine to choose for Django app? - Stack Overf...

python database django sqlite3
Rectangle 27 0

uuid_short() produces a bitwise conglomeration of the server ID, a fairly static time component, and a sequentially increasing 24 bit integer. These bits are stuffed into an 8 byte integer. The time component is based on the server's boot time.

uuid() produces hex string that represents a 16 byte version1 UUID. Version 1 UUIDs are a bitwise conglomeration of the server ID, the current timestamp, a few bytes that come into play if you generation IDs at hyperspeed, and a few utility bits.

To answer your question: does uuid_short provide time and space uniqueness that rivals uuid? The answer is no. Case in point, the server ID in a uuid_short is only one byte. So if you have 256 or more servers, at least a few of them will have the same node id, which means you lose space uniqueness. For comparison, the server ID in version 1 UUID is 6 bytes long, effectively killing the chance of duplicates for all but the largest of corporate server farms :)

A better question is whether uuid_short is good enough. You could see ID collisions if you :

  • Generate more than 16 million IDS from the same server in little time. ***
  • Boot servers with the same server ID all at exactly the same time, and share data between them.
  • Fiddle with the system clock and then reboot your server.

The second issue seems unlikely for most people, but the first one is worth investigating before you commit to making uuid_short the basis of your keys.

*** Based on the mysql docs for uuid_short, it seems like you would see collisions if you generated more than 16 million IDs during the uptime of a single server. But that would be silly. The mysql docs go on to say that you are fine as long as you don't generate 16 million IDs per second. That implies that they must bump some of the bits in timestamp if you exhaust the 16 million sequential IDs. I have not tested this.

sql - Is Mysql UUID_SHORT() comparable to UUID() - Stack Overflow

mysql sql database guid uuid
Rectangle 27 0

This would be a simple query in Oracle ( http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm) since it supports hierarchical queries using "CONNECT BY" but I don't think there's a comparable solution for MySQL. It looks like you're going to do something really inefficient or you're going to have to modify your table structure to support this specific function.

mysql - SQL: Select SUM of all children records recursively - Stack Ov...

sql mysql oracle tsql hierarchy
Rectangle 27 0

Used often in combination with mySQL code, but seems always forgotten in comparable situations:

$result = mysql_query($sql) or die(mysql_error());
if(!$foo = SomeClass::bigQuery())
    $foo = new EmptySet();

ah, you're right that is always forgotten. Nice!

$foo = SomeClass::bigQuery() or $foo = new EmptySet();

ternary operator - Conditionally assigning PHP values - Stack Overflow

php ternary-operator
Rectangle 27 0

As a rule of thumb, the less queries the better. There is an overhead for passing a query to MySQL, however complex the query is. However php is surprisingly fast for some things, and if you are not using indexes for a sort (which sounds possible if you are effectively sorting the results of several queries unioned together) the performance of a sort in php might well be comparable or even better.

Where there is a big difference is where you get the results of one query and then perform another query for each returned row on the first query. It is quite easy in this situation for the number of queries to get out of hand rather rapidly without being noticed. At work I found a menu generation script that had one query to get the high level menu items and then another query for each high level item to get the child menu items. This was easily rewritten as a join, but the surprising part is the performance difference, with the time taken to generate the menu dropping from 0.2 seconds to 0.002 seconds.

But it is a case by case decision. I had a requirement to return some values based on a levenshtein calculated value (essentially a score of how different 2 strings are). Using a mysql custom function this was possible and greatly reduced the number of rows returned but was quite slow. The php levenshtein function is massively faster, and it proved to be more efficient to return several times as many rows and then process them in php to get the levenshtein value and then drop the no longer required records.

In the situation you describe I suspect the difference might be marginal. It would appear you will only be doing 4 queries rather than 1 more complex query. However without seeing the table structures and queries (which unfortunately you can't provide) it is difficult to be certain. It might well be efficient to do a single reasonable complex query but ignore the sorting where not strictly necessary, and then perform that in php (usort with a user defined comparison can be useful for this).

There is the further issue that a complex query is more difficult to maintain. While there are plenty of people who can nail a php script together or who can understand a simple SQL query, the number who can understand complex SQL queries is worryingly small.

Thanks for your answer. I am planning to roll on small queries, "as is", because I've run a quick test on how faster could be a couple of combined queries with sorting/filtering (some WHEREs couldn't be totally matched) and it showed up particularly slower than before, actually (by 50%, on a 1000 "benchmark run").

mysql - What is faster, a big joined query with more PHP or multiple s...

php mysql left-join database-performance
Rectangle 27 0

Mark, I'm taking a long shot here. For what I know of Classical ASP (which consists in years of swearing and loving), a NULL answer from the MySQL database results in... nothing, actually. Unless you define the recordset result as a string, it won't be comparable with a "". So, either you do this:

var recordsetVal = oRs("textthing")&""

... or you could simply do this:

if oRs("textthing")&"" <> "" then

Also, instead of while ... wend, use a do while...loop lace. It might work. Again, this is a long shot. many factors could be involved in this question, but MySQL Driver and ODBC compatibility issue is only one of them and, fortunately, the least probable. Keep us posted.

Thanks for the response Eric. In this case, there IS text in the response from the database (because it's a fictitious example). But the point is, it's there for access 1, but not anything further. :( I have edited the question code to reflect this more obviously.

Hmmmm,... thats odd. I could have sworn it was a null-type string problem. Honestly, I'm lost here... I'll try to think a little further here. Oh, BTW, you've sliced out the rs.MoveNext from the edited code. I know it was there before, but if someone else comes in and it is not there, they might think that that's where the problem at.

Yes, no movenext is intentional - since I am selecting 1 thing, and showing what happens with that. (I'll change it so it's a string and people will get the idea)

ASP (Classic) RecordSet value 'disappears' after first access, when My...

mysql asp-classic odbc
Rectangle 27 0

This shows how to do it in SQL2000. I'm not completely familiar with MySQL syntax but I'm sure there's something comparable

How to remove duplicate entries from a mysql db? - Stack Overflow

mysql duplicate-removal
Rectangle 27 0

Regarding reliability, I haven't had any problems with downtime. As a result, I have no first hand tech support experience with GoDaddy, but from what I read on the boards their tech support is pretty good (comparable to any other tech support I guess). They offer LINUX and Windows hosting (if that matters to you), MySQL and MSSQL database support, and .NET 3.5/AJAX.

And the price was reasonable, as far as I'm concerned.

Reliable and performant cheap (ish) hosting for ASP.NET 3.5 and mysql ...

hosting asp.net-2.0 web-hosting shared-hosting
Rectangle 27 0

Innodb is a better choice as MYISAM is really old engine and would fade away in near future. You can look into sphinx with Innodb or just migrate to MYSQL 5.6 the new stable release by MYSQL to take advantage of new Innodb full text search feature and it is quite comparable to sphinx.

mysql - MyISAM tables are better then InnoDB tables for full text sear...

mysql full-text-search innodb myisam