logs archiveChat DB / Freenode / #mysql / 2015 / October / 13 / 1
mehwork
is there a way to 'namespace' databases in mysql? For example, if i need to install two databases with the same name?
Xgc
buzzkill: SQL deals in loops too. You just need to understand how to read it as a series of loops.
buzzkill
Xgc: I understand... but I don't grasp it. I realize this is me being thick and the problem exists on this end. =)
Xgc
buzzkill: It's not just you. Most people find it difficult to get over the initial hurdles.
buzzkill: It's just operations over sets. That's the basic loop. Pretty much every query can be broken down into one or more of those.
buzzkill
ok... frustration is over. Understanding is still close to nil. I was able to adapt one of the examples. I _think_ I have the results I am looking for, but I need to verify.
Xgc
buzzkill: We can usually read SQL more easily inside -> out. Find the inner most set and the associated operation(s). That will produce a partial / intermediate result (set). Now move to the next outer operation that operates on that intermediate set.
buzzkill
Xgc: the funny thing, I used to write LISP. So inside out and RPN are familiar.
Xgc
Continue until you have no next outer operation. That last intermediate set is the final result.
buzzkill: Yes. But any language is tough to understand if you take it as one large blob. SQL is no different. If you can identify the smaller components, you'll be able to fit them together more easily and stepwise build on that until you have the full meaning of the statement.
buzzkill
I'll keep chipping away. Thanks for the encouragement.
Xgc
The trick is to find and understand those smallest components.
It's one of those areas that, at first, seems like complete non-sense, except for the most trivial SELECT a FROM x;
buzzkill
argh! like this, when trying to create a view: View's SELECT contains a subquery in the FROM clause
Xgc
buzzkill: that's a good example of a simple concept, if you look at it the right way.
buzzkill: A subquery in the FROM clause is called a 'derived table' for a reason. It's just a table that is derived from a subquery.
buzzkill: So think about creating a temp table with that subquery. Now replace in your larger select that subquery with a reference to the temp table.
buzzkill: SELECT ... FROM (SELECT ... FROM t2 ... ) AS t1 WHERE ...; becomes CREATE TABLE t1 ... SELECT ... FROM t2 ...; Then the outer query becomes something as simple as: SELECT ... FROM t1;
buzzkill: That subquery is nothing more than just another table. It's just not a base table in your schema. It's calculated on the fly.
buzzkill
Given that these values are updated once per month, from a programming standpoint, would it be better to just pass that query, or to build the temp table, insert, then query? I would think, given my scale ~20 players X 11 months, handling it within the program is the better choice... no?
Xgc
buzzkill: Yuo could create the temp table if you wish. But there's no need, from a logic perspective.
buzzkill: With MySQL versions prior to 5.7, using that temp table might even improve performance significantly.
buzzkill
I can grasp scale and how it would be far more important with tens of thousands of rows, but my 20 row result is memory happy.
Xgc
buzzkill: The first trick is to understand that both forms are logically identical. So you should not have much trouble understanding either.
buzzkill
The temp table, and subsequent query could/should live in a procedure?
thumbs
!t buzzkill sp
ubiquity_bot
buzzkill: http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/
buzzkill
lol
okay.
thenewone
thumbs, Hi
:D
Xgc
buzzkill: Avoid procedural solutions. Don't avoid learning the right approach by reverting to explicit procedures and loops.
buzzkill
Xgc: I guess my thought process here is like the chicken and egg discussion... what should live on the server side, what should live on the client side, and what makes better use of the resources available. Again, with my example, this is not a memory/CPU/Disk hog. If I were trying to correlate purchases for Amazon, I can see the temp table approach being far more robust.
Xgc
buzzkill: You have multiple variables involved which can conflict with each other.
dagb
Working in python here, and could need a bit of advice.
I have a number (<10000) of items with a simple, fixed set of attributes. This is simple to map to a table.
Xgc
buzzkill: From a functional perspective, it's more a matter of whether you need to explicitly break the query into these structures or whether it's easier to let the system do that internally.
dagb
But for each item, I also have two variable length (1-50 keys) dictionaries with a ~5-item tuple per key.
What is the best structure to accomodate the dictionaries? Is my dataset so small I might as well put each key,value par in a separate record and identify them all by the id of the main item?
I.e. put the dicts in a separate table?
Xgc
buzzkill: As an example, suppose that SQL allowed you to specify the exact memory allocated for all the various internal structures required to process your query. You could provide all that, if you wished. But you might consider it a waste of time if the system can do that for you.
buzzkill
Agreed.
Xgc
The same goes for creating temp structures that just hold preliminary results, assuming there's no great benefit to creating them and maybe keeping them yourself for later use.
buzzkill: If you read a little about MySQL 5.7, there's some new optimizations around 'derived tables', the type of internal temp table we are talking about.
buzzkill
ok, I'll look into it. Thanks again.
Xgc
buzzkill: It turns out that you actually can benefit from doing some of that manually / explicitly, because prior to 5.7, the engine doesn't "do the right thing".
dagb
virtual columns works well in that other database..... :-)
Xgc
buzzkill: You see, there's no one answer. Both approaches can be useful.
snoyes
dagb: virtual columns coming soon to a 5.7 installation near you!
thumbs
snoyes: dang
snoyes
both materialized (and so indexable) at insert time, and generated on the fly at select time
bradfordli123
I created a foreign key in my table. When I describe the table, i see `MUL` under the key column. What does this mean?
thumbs
bradfordli123: it means don't use DESC on tables.
snoyes
thumbs++
thumbs
bradfordli123: use SHOW CREATE TABLE tbl\G
snoyes
bradfordli123: it means it's an index that is not primary or unique
"If Key is MUL, the column is the first column of a nonunique index in which multiple occurrences of a given value are permitted within the column." - https://dev.mysql.com/doc/refman/5.6/en/show-columns.html
thumbs
but really, don't use SHOW COLUMNS nor DESC tbl
bradfordli123
thumbs: thanks for the tip!
snoyes: thanks!!
I am trying to make my foreign key column unique as well. I am trying `ALTER TABLE account_activation ADD UNIQUE user_id;` but I am getting a syntax error. Is this the best way to alter a table?
MatheusOl
bradfordli123: UNIQUE (user_id); IIRC
bradfordli123
MatheusOl: Thanks!
mehwork
is there a way to 'namespace' databases in mysql? For example, if i need to install two databases with the same name?
dafr
no
Azrael_-
quite often you prefix the tables for this
dafr
schema names have to be unique
mehwork
k
dafr
but why do you want to have the same schema name more than once?
mehwork
that doesn't work if an application expects them to be named one thing though
dafr
the application shouldn't have any (non configurable) dependency on the schema name
are you sure you're talking about databases / schemas and not tables?
mehwork
i see what you're saying
i think i can point this app's config at any db i want
rodney77
Hello, I am trying to accomplish the reverse of the LIKE clause. so, for instance, WHERE post_title LIKE '%imitri' will match post_title 'dimitri'
Xgc
rodney77: Try UNLIKE
rodney77
however, i want to do the opposite, where you're trying to match 'superdjdimitri', if 'dimitri' is anywhere in there, then WHERE will return my result
Xgc
rodney77: That doesn't sound like the opposite.
rodney77
thanks, Xgc, it's not exactly the opposite
but do you see what i mean, though?
Xgc
rodney77: Do you mean WHERE 'blah' LIKE CONCAT('%', colname, '%')
rodney77
Xgc, I will try it right now...
Xgc
rodney77: I can't tell what logic you wanted and where the data resides.
rodney77:
rodney77
ok Xgc, so let's say the value of the column post_title is 'alexander'
and the WHERE term is 'alexanderjohnson'
Xgc
rodney77: "trying to match 'superdjdimitri', if 'dimitri' is anywhere in there" I can't tell what your table provides and what query literals you have.
rodney77: Ah. I guessed correctly.
rodney77: So the column contains the substring and the literal is the larger string in which that substring might be found.
rodney77
Xgc, exactly.
Xgc
rodney77: The form I showed is fine. You can also use string functions to locate the index of a substring.
rodney77
a ha
i didn't enter it properly. your solution is correct. thank you, Xgc
Xgc
rodney77: You're welcome.
rodney77
Xgc, i have another question. is it possible to use your example with spaces?
using our previous example, let's say the substring is not 'alexander' but 'paul alexander jacob'
Xgc
rodney77: spaces are valid within strings and patterns.
rodney77: As long as you don't want magic to understand what you are thinking, that each word is handled separately.
rodney77
thanks, Xgc. Using your example, if my string is 'alexanderj' and my substring is 'paul alexander jacob', it won't work anymore. so maybe i need to go the route you mentioned, using string functions.
dafr
rodney77 if you really want to ignore whitespaces you could simply remove them with replace()
« prev 1 2 3 4 5 6 7 8 next »