|
Two ways: A) Modify the data before insert in one transaction. Start a transaction, clear out all old rows (should be just one with this algorithm) in the same day, insert a new row, close transaction. B) In an external join table, enumerate the days you want to view. Then join those days to an aggregate table of days and their maximum time. This will pull out just the ones you want without deleting the data.
|
# ? Feb 14, 2009 16:40 |
|
|
# ? Jun 8, 2024 09:02 |
|
Anyone know how I can make this line ANSI '99 compliant?code:
|
# ? Feb 19, 2009 15:45 |
|
Hey how would you do this SQL:code:
But in another table called {term_node} I want it to select only the titles that correspond to when the term_node.tid is the same as that of the original nid (8109). Basically, there are thousands of nodes, and some of them have the same term_id. So, if I am looking at a certain node, I'd like to find the other nodes that have the same term_id. Hope that makes sense. Hopefully you can kind of tell what I am doing by my bad syntax. I tried "IN" and "=" in the middle part of the query. I also tried it with and without the ";", etc. I'm just not sure how to do this. Also, with the middle Query, there can be multiple rows corresponding to this.
|
# ? Feb 20, 2009 20:01 |
|
Try it with parenthesis () not curly braces {} and without the ; in the sub-query. As an aside, because your WHERE condition refers to the outer table of your LEFT JOIN, you have essentially turned it into an INNER JOIN. Jethro fucked around with this message at 20:31 on Feb 20, 2009 |
# ? Feb 20, 2009 20:28 |
|
Jethro posted:Try it with parenthesis () not curly braces {} and without the ; in the sub-query. Wow it worked. I love you.
|
# ? Feb 20, 2009 20:58 |
|
Jethro posted:Try it with parenthesis () not curly braces {} and without the ; in the sub-query. Hmm, I'd like to take this to the next level.. The "tid" is a term ID and it can have a parent and even a grandparent in its hierarchy (eventually even more levels of ancestors) Within that query, how can I order the tid's to have the ones with the most number of "levels" on the top? So if tid #10 has a parent tid #5 and a grand parent tid #1. I would like that listed above tid 5 (which has only 1 parent, tid #1). Thanks again EDIT: I should mention there is another table {table_hierarchy} that has two columns, one for the current tid and another for the direct parent of that tid (which could be the child of another tid too and I'd have to recursively check that parent's tid as well). This reminds me of a breadth search or one of those binary tree searches from my computer science class way back when. I wonder if there is a function that can do something like that already? cannibustacap fucked around with this message at 22:18 on Feb 20, 2009 |
# ? Feb 20, 2009 21:13 |
|
cannibustacap posted:Hmm, I'd like to take this to the next level.. You'd want to write a scalar-valued function per tid that loops through the multiple levels of ancestry and returns that number, then order by the function.
|
# ? Feb 20, 2009 22:11 |
|
cannibustacap posted:This reminds me of a breadth search or one of those binary tree searches from my computer science class way back when. I wonder if there is a function that can do something like that already?
|
# ? Feb 20, 2009 22:25 |
|
I have a table of Users and it has a lot of columns that represent 1/0, true/false settings; like whether the user is banned, whether he is validated, and a whole slew of preference settings that can be either on or off. I've set the columns as TINYINT(1); so basically there are a bunch of columns the size of a single byte that are being used to hold boolean values. Is this particularly inefficient compared to storing these settings, or groups of them, as a single number which can be decomposed into a sum of powers of two ("bitwise" interpretation of numbers I think this is referred to)? Or is the difference negligible? Using MySQL if it matters.
|
# ? Feb 21, 2009 00:56 |
|
Hammerite posted:I have a table of Users and it has a lot of columns that represent 1/0, true/false settings; like whether the user is banned, whether he is validated, and a whole slew of preference settings that can be either on or off. I've set the columns as TINYINT(1); so basically there are a bunch of columns the size of a single byte that are being used to hold boolean values. Is this particularly inefficient compared to storing these settings, or groups of them, as a single number which can be decomposed into a sum of powers of two ("bitwise" interpretation of numbers I think this is referred to)? Or is the difference negligible? If you can model the info by replacing "Boolean" columns with one or more encoding schemes, and/or detail tables, it's more normalized and *may* perform better. For example, if the table models a single status element in several mutually exclusive columns, you can make up codes and use one column. Or if you have a set of boolean values that are not mutually exclusive, but model the same kind of thing, you can often use a detail table, again with an encoding scheme. For example, if a user can be either Active, Banned, Suspended or OnVacation (I'm pulling these out of my rear end) and these are mutually exclusive, you could make up an encoding scheme and replace them with a user_status column. Or if a set of the columns model user Interests, and they're not mutually exclusive, you could again make up an encoding scheme and use a UserInterests detail table. Each row represents a user interest. This is more normalized and may perform better, and part of the reason is because you can get good selective index(es) on the new columns.
|
# ? Feb 21, 2009 03:38 |
|
It would be horribly unnormalized to do something like this, wouldn't it: Interests: Driving = 1 Flying = 2 Gaming = 4 Movies = 8 Baseball = 16 etc etc, so someone who likes driving, gaming and baseball would have an entry of 21. I guess this would only be useful if the decoding had to be done in runtime. And even then, no.
|
# ? Feb 21, 2009 04:09 |
|
Golbez posted:It would be horribly unnormalized to do something like this, wouldn't it: Yeah, theoretically, because it stores more than one attribute in a single column, and practically because you would have to mess with encoding and decoding, and writing queries is a bit of a hassle. Plus, adding a new interest is a pain in the rear end. If you use a detail table and have a bunch of interests, then you get a good selective index and you would want to consider that design.
|
# ? Feb 21, 2009 04:19 |
|
Thanks for the answers. I have another, unrelated question. I recently was obliged to restore my database from a backup copy and have just found out that throughout the replacement version, wherever the pound sterling character £ should appear it has been replaced with a question mark. Thankfully, the question mark is a character that ought never to appear anywhere so I was able to correct this using a simple query (UPDATE TableName SET ColumnName = REPLACE(ColumnName, '?', '£')). But why did this happen in the first place, and how can I prevent it happening the next time I either back up the database or have to restore it from backup? Looking at the SQL export file that forms my backup copy, I see that the fault exists in the backup copy of the database. The backup was produced by phpMyAdmin's export facility. All of the tables in this export file are specified in the following way: CREATE TABLE IF NOT EXISTS TableName ( {some stuff} ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT={some number} ; I'm assuming that the problem arises out of some character encoding issue. So is there some way I can set the character encoding of my tables so that this won't happem in future, or is it a limitation of the method I am using to back up the database? I was previously unaware of any need to supervise a character encoding, as no problems have been evident up to this point.
|
# ? Feb 22, 2009 02:02 |
|
Is that character's code > 127? I'm guessing so. Different programs interpret > 127 characters differently. I'm not even sure there's any kind of convention. You'll probably have to look in the documentation of all the tools in the chain that you are using and verify they all agree upon how to encode characters. Also read Joel on Unicode.
|
# ? Feb 22, 2009 02:42 |
|
Reading on Wiki I note that the £ symbol can be reliably obtained in HTML, even in a textarea environment, using the code £. Perhaps I ought to consider moving to this way of storing the symbol in the database. EDIT: This is what I've chosen to do. Hammerite fucked around with this message at 17:51 on Feb 22, 2009 |
# ? Feb 22, 2009 03:21 |
|
Those are called character entity references; I often see them called "character entities" for short.
|
# ? Feb 22, 2009 23:27 |
|
Hypothesis: A query can only use one index per instance of a table in a query. Confirm/deny? If I have a query: select ... where id = ? and group_id = ? And I have two indexes, one on id, one on group_id, will the query only use one of them or both? To me, the proper solution would be to use an index that's a composite of group_id -> id, but I'm not asking about that. Edit: After doing a bit more Googling, it seems that SQL Server and Oracle can both take advantage of multiple indexes for one query. So yeah, neat. Triple Tech fucked around with this message at 20:15 on Feb 23, 2009 |
# ? Feb 23, 2009 19:51 |
|
It all depends on the table statistics. It isn't always most efficient to use an index! If you want to go nuts, create an index on both columns. However, you know the rule about the more indexes...
|
# ? Feb 23, 2009 19:52 |
|
Regarding my character encoding problem, I noticed that all accented characters that my users have typed (I have users speaking a variety of languages) were also changed to question marks in the backup. I could use character entities for these too (I found out that PHP has a function that does this), but that seems just OTT. So looking around on the Internet, I saw commentary on changing the character encoding of MySQL tables. I've converted all of my tables from latin1 character encoding, latin1_swedish_ci collation to UTF-8 character encoding, utf8_general_ci collation. I made a backup copy both before and after doing this; I was pleased to see that in the "after" backup, the accented characters have come out correctly. But I was also confused when I noted that in the "before" backup, at which time the tables were still in latin1 encoding, the accented characters also survived - which is in contrast with what I expected would be the case given their state in the export file a couple of days ago when I first found the problems. I guess I don't know whether I've made the slightest bit of difference by changing the character encodings of my tables.
|
# ? Feb 23, 2009 20:06 |
|
Read what I said before: some code in the "pipeline" or "call chain" was probably loving things up. Accented characters have codes > 127.
|
# ? Feb 23, 2009 20:40 |
|
Victor posted:Read what I said before: some code in the "pipeline" or "call chain" was probably loving things up. Accented characters have codes > 127. I read it, and I appreciate it; I'm just remarking that I found it surprising that I got a different result re:accented characters the two times I made a backup from the latin1_swedish_ci database despite thinking I was doing absolutely everything just the same. Of course, I probably did something subtly different the 2nd time, or looked for the wrong things in the export file. Edit: I did some experimenting with a test database and couldn't reproduce the "replace with question marks" behaviour at all, although the export function does behave better in general when the table is defined to be UTF-8, so it seems like the conversion was a worthwhile thing to do... Hammerite fucked around with this message at 23:00 on Feb 23, 2009 |
# ? Feb 23, 2009 22:24 |
|
I'm sort of stuck on an SQL Server 2005 problem. We have a stored procedure that runs in a job every 5 or so minutes. It's painfully slow, taking a minute or so to complete, even though the select statement driving it runs almost instantly on its own. My boss wrote the code, so some of this I'm not sure about, but I'm pretty sure the cursor is the problem. Sadly, I'm not sure how to do this without using a cursor. SQL isn't my strong suit. code:
|
# ? Feb 24, 2009 00:20 |
|
glompix posted:I'm sort of stuck on an SQL Server 2005 problem. Can you post DDL, including the CREATE PROCEDURE statement for "Createrequestfromwebrequest"? The ideal would be do replace the procedural, one-record-at-a-time processing with set-based processing, such as an INSERT statement that acts on the whole set of 'webrequests' that do not reference a row in 'requests' (not having any DDL, I'm guessing).
|
# ? Feb 24, 2009 02:25 |
|
Victor posted:However, you know the rule about the more indexes... I don't think I'm entirely sure what you mean. It kind of sounds like a "Too many chefs in the kitchen" thing but I just want to be clear.
|
# ? Feb 24, 2009 02:58 |
|
I think he means a superfluous amount of indexes will never decrease read performance, but it will decrease write performance.
|
# ? Feb 24, 2009 03:11 |
|
So index the important stuff then? Like primary/alternate keys, fields you know are going to involve queries, etc.?
|
# ? Feb 24, 2009 03:29 |
|
Sergeant Hobo posted:So index the important stuff then? Like primary/alternate keys, fields you know are going to involve queries, etc.? PKs almost always create an index automatically. I normally throw indexes on the columns that end up in where and group by clauses the most.
|
# ? Feb 24, 2009 04:11 |
|
I only know three cases for a "short" rule of thumb... 1) Things you know you're going to search by. Usually category_id, id, state (a type of category), pseudo keys (last name) 2) Things you want to "cover" (technical term). If you want "birthday by user id", you create a composite index of "user_id -> birthday". This functions as both your user_id only index as well as an optimized case when you want just the birthday and nothing else. That's called a covering index. 3) When you want to "defrag" a table by re/building a clustered index.
|
# ? Feb 24, 2009 04:25 |
|
glompix posted:
|
# ? Feb 24, 2009 09:26 |
|
Sergeant Hobo posted:I don't think I'm entirely sure what you mean. It kind of sounds like a "Too many chefs in the kitchen" thing but I just want to be clear. measure measure measure ...
|
# ? Feb 24, 2009 09:27 |
|
Victor posted:
Holy poo poo, thank you thank you thank you. I didn't know about this keyword, but it made that procedure go from running in ~50 seconds to running practically instantly. That CreateRequestFromWebRequest procedure is pretty huge and we wanted to keep it separate. Now we can have our cake and eat it too!
|
# ? Feb 24, 2009 17:58 |
|
Yeah, I've never dug into the details of cursors because I have never needed anything besides FAST_FORWARD. If you need another kind of cursor, you might just be doing it wrong.
|
# ? Feb 24, 2009 19:48 |
|
I have a query that looks like thiscode:
I have been trying to figure this out for a few hours and google searches land me on a lot of examples that don't really make sense or apply. Sorry if it's a retarded question, I don't normally do SQL for my job so this thing is killing me. edit: SQL 2005
|
# ? Feb 25, 2009 01:05 |
|
Let me know if this post helps.
|
# ? Feb 25, 2009 01:41 |
|
Trying to work with MySQLi prepared statements. I figure I could use it for all my INSERT/UPDATE queries (no matter how small) as it seems to be the best way to prevent injection attacks (am I right believing this?). I have the following code that is not working with my UPDATE statement...code:
A similar SELECT query works fine. I only have two ?'s and two binded variables, I can't figure out why it's not liking that.
|
# ? Feb 25, 2009 02:20 |
|
VerySolidSnake posted:Trying to work with MySQLi prepared statements. I figure I could use it for all my INSERT/UPDATE queries (no matter how small) as it seems to be the best way to prevent injection attacks (am I right believing this?). I have the following code that is not working with my UPDATE statement... You need to bind parameters using mysqli_stmt_bind_param. mysqli_stmt_bind_result is used to execute a query and insert the resulting columns into variables. I think.
|
# ? Feb 25, 2009 05:30 |
|
Just a quick question: I have a set of data with about 2 billion rows, indexed by a user account ID. (The total size of this is expected to be around 60gb, if that helps) Once per day I will insert about 10-12 million rows in one go. I currently have all this data in one table (No expensive queries will be needed on this, only lookups on the single index), would it give any performance boost to split this table into, say, 10 or 100 smaller tables?
|
# ? Feb 25, 2009 14:22 |
|
Victor posted:Let me know if this post helps. Yes it helped I used this code:
MrHyde fucked around with this message at 19:11 on Feb 25, 2009 |
# ? Feb 25, 2009 17:25 |
|
moostaffa, it's called sharding or partitioning -- these two search terms should help you look for stuff on the internets.
|
# ? Feb 25, 2009 19:10 |
|
|
# ? Jun 8, 2024 09:02 |
|
Is it possible to do something like:code:
code:
code:
MrHyde fucked around with this message at 19:57 on Feb 25, 2009 |
# ? Feb 25, 2009 19:48 |