I have a question related to mysql indirecty I have a Mysql 5 DB which on I can connect from a distant place
Habbie, not to worry, your answer was impressively fast!
quick doubt.. if i left join table a with table b and table b contains 2 rows for table a… and then i group by table a field.. wich id of table b is choosen ? highest ?
for example.. select eye.id from human left join eyes on eye.human=human.id where human=1
groupwise max
http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
and eyes table is 1 | left – 2 | right
it will choose left or right eye ?
ok, archivist, reading
mmm, archivist, i understand those websites, but i’m not using max at all
but you want to group AND define which record you get, its the same problem
no.. the thing is i just left join and group by human
i don’t define wich eye i want… and mysql works fine, but i’m unsure wich eye it selects.. the one with the higher id, lower… etc
hence the need for the groupwise max doc to teach you to make that decision
mmm, sorry, i understand maybe is some RTFM on my side… but i don’t want to make that decission.. imagine the example of the humans/eyes
i just want to know if a human have eyes or not
ie, know if field is NULL or have any id
but i’m curious about the fect of… being two eyes, wich id php mysql web hosting selects, the one for the right or the one for the left eye ?
if you are sure i need max for that, i will read again, is just i’m not sure
its random, you need to decide
hey is there anyway to find a mysql servers IP address via phpmyadmin? all i can see from it is 127.0.0.1 cause it’s local ~_~
it’s random ? nice ! that’s what i wanted to know
MarcosBL, a better term would be undefined not random
mmm… but it returns an id
it must choose based on something…
MarcosBL, there may well be an insert order or whatever, but to not break an application it is up to you to choose/write sql hosting so that the result is fully defined
i know , archivist, in this case is enought for me to get the number of nulls and not nulls, was just wondering why it choosed and id or another
users (id, mail, etc.) and misc (id, etc). How can I make a select which does this: select users.id if in table misc there is no id that matches users.id
a not in b
SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;
archivist, just curious, i have made a test case, mysql chooses the first one that fits the condition, no matter how many it find after that
but define first
archivist, that generates an error
the first index that fits the condition
in my case a ID field index
i suppose it just gets the first inserted
if you don’t specify a order by… if you do, it gets the first that matches the order by… i have just tested
hi!
“price_interval” {day,fixed}. I want to create a select with a result like: “price is (per day|fixed): $[price_field]“. Is this possible?
i’ve set master-master replication, but they don’t seem to be replicated
anyone to help me ?
no ?
can i run BerkeleyDB and mysql at the same machine . using RHEL4?
zeeesh, sure
Sp4rKy, no idea, sorry
:/
can anyone suggest how I can grab now(), but have it accurate only within 5 minute divisions ?
p0windah, use a modulo
against the timestamp?
yeah
select date_format(date_sub(now(), interval (minute(now()) % 5) minute), ‘%Y-%m-%d %H:%i:00′);
If you have a problem, describe it completely.
Maybe you’re using mysql version 0.0001 that doesn’t support SELECT. Maybe you don’t like the color of the output. We can’t tell from your description.
syntax is ok, works for me TM
lol
Xgc, I dont have a problem, otherwise I would have stated it clearly
works for me too, both mysql 4.something and 5.something
Xgc, I asked a question earlier, someone gave me a tip; soI went away and wrote a statement and thought I would paste it back into the channel
anyway, archivist & MarcosBL thanks
np, man
it’s my cheap my way avoid page spamming
The suggested approach is to test yourself and then ask if you have problems. There’s a risk in your current approach.
Xgc, I did test myself, and it looked good
Xgc, I just wanted to show what I had come up with and see if anyone could spot any obvious caveats
maybe is just a bad redaction, it could be better a “I have wrote this.. it will work in any mysql version?” or something like that
I guess so, but since I didnt notice anyone else that posted SQL in the backlog stating precise versioning, didnt think it was necessary
Xgc, maybe you can add this into the topic
stay cool in the heat of the battle x)
good morning
maybe yours.. mine is being longgggg…
does sending a HUP signal cause mysql to re-read it’s config?
You misunderstand. I was simply trying to avoid ignores you’ll run into by posting untested SQL continuously or tested sql just to find out what you already know. If this isn’t your normal approach, don’t worry about it.
Xgc, I’m being argumentative. so I’ll say sorry and attribute it long hours of overtime.
No problem.
how “bad” is it to store files in the database?
images
http://hashmysql.org/index.php?title=Storing_files_in_the_database
archivist, images and documents, id like to attach the data to my reports in the database because this would make a lot of things easier
the_wench, thanks
It seems to: http://lists.mysql.com/commits/22551
Note the: ‘reload’|'force-reload’) kill -HUP $mysqld_pid && log_success_msg “Reloading service MySQL”
What is name of the system table that contains structures of user tables ?
hi
# Time: 041222 17:27:35
as found in mysq-slow.log
what’s the 041222?
22 december 2004, spike
oh, right year/month/day
shame on me, sorry
Excuse me, where do I find names of system tables in mysql doc , or how do I list names of system tables ? ‘SHOW TABLES’ does not show me system table ?
from command line… USE mysql; SHOW TABLES; ?
thanks
np
i am trying to obsain list of table columns in dbi
how do i do it
In 5.0+ you have access to information_schema.
i need something simple and cross-versions
maybe execute statement “describe XYZ” ?
SHOW CREATE TABLE dbi;
You get the entire structure of the table that way
i need only list of column names
in dbi
Sure. Also: show create table tblname; and show columns from tblname;
oh cool
describe TBL; and show columns from TBL; seem to produce the same ?
is there system-table that keeps structure of all user tables ?
Not across all versions.
That would be information_schema in 5.0+.
will a HUP signal cuase mysqld to re-read the .cnf file?
Asked and answered.
eh? didn’t see it
Note the: ‘reload’|'force-reload’) kill -HUP $mysqld_pid && log_success_msg “Reloading service MySQL”
aha
It seems to: http://lists.mysql.com/commits/22551
it scrolled off my screen, sorry
hello. I’ve got trouble with ecoding when exporting and importing a database.
is the some FAQ for dummies or something like that on the subject?
*is there
No problem.
cool, i tried multiple searches and couldn’t find an answer
or can anyone here walk me through?
does someone knows a tool to create a model from a database?
(a graphical model like ER)
dbdesigner
but theres a successor of it on mysql.com but i dont remember the name of it
yeah, but I can’t use a different port..
ah
wow even php mysql web hosting 6?
you can afaik, just enter it in the connection menu
it’s disabled somehow
dbdesigner became Workbench, which is still in alpha
please, i’ve a strange pb
i create a replication user
i can login with it from the slave server to the master on commandline without pb
snoyes, i know, but it works, tested it some month ago, do you have a link to an new build?
but it can’t logging itself (i get an access denied)
http://dev.mysql.com/downloads/gui-tools/5.0.html#Workbench
is mysql workbench crossplatform?
snoyes, lol, thank you, im already on that page but havent seen it
whereas the configuration use same values than the command line
Implementations are platform specific, but the communication is platform independent.
okay nice
I want a number colum that when I add something into that table count up automatic. So if I have 5 rows in that table and I add a new row that field will have value 6. Anyone know what I am looking for?
Just be careful of major version differences.
phpmyadmin writes me this message
#1045 – Access denied for user ‘root’@'localhost’ (using password: NO)
depaulis, autonumeric column
where can i set password to yes?
Thanks.
put in a password when connecting
where?
i think i need a name of a script where are mysql users set
http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html
phpmyadmin must provide a way to enter user and password when connecting. These are two separate issues.
is it known that mysql odbc archives on dev.mysql.com are all corrupt?
Which mirror?
i’ve installed horde which uses mysql. my tables are damaged after a crash. is there a way to repair it again_
how do I import an sql file into my database from command line? I know as much as mysql; use database, after that I have no clue
source /path/to/file
or, on the command line, mysql dbname /path/to/file
-p ./mysqlData.sql
ah cool thanks
./mysqlData.sql
most of them
they all give me exactly the same zip file (same checksum)
but never mind I installed the beta 5.0 driver, it works fine, I gave up trying to install the 3.51
thanks again
hi. (first of all excuse me for my bad english) I have a linux machine with mysql 5.0.41 server. I have given the permissions to root with the command: INSERT INTO user (Host,User,Password) VALUES(‘192.168.1.114′,’root’,PASSWORD(‘root’)); But when I try to accessthe db from that machine with
root user mysql throws me “access denied for user ‘root’@192.168.1.114′ (using pasword: YES)”. Has snybody an idea on how to solve this. than
ks in advance
did you issue FLUSH PRIVILEGES;
ah no
And did you set all the _priv fields to ‘Y’?
It’s much easier to just use the GRANT syntax. GRANT ALL ON *.* TO ‘root’@'192.168.1.114′ IDENTIFIED BY ‘rootPassword’;
thanks snoyes, will try with that
tete
I want that DB say ..ok 1 response for tété…
GRANT syntax worked ok. thanks!!!
I think you will have to make a script (perl python bash java…) that transform a “accent word” to “unnacent”…
ok ok sgx___ i use php for this .. but
ok ok sgx___ i use php hosting for this .. but
I have a table with e-mail addresses and total e-mails received per day. I want to find the top 5 recipents overall. I have the statement SELECT SUM(`count`) FROM `topemailrcpts` WHERE `recipient` = ‘username@host.com’; How can I find the sum for all recipients?
sgx___, but if I transform “tété” to “tete” in textField with ereg_replace
sgx___, it’s not real good solution
SELECT recipient, SUM(count) AS totals FROM topemailrcpts GROUP BY recipient ORDER BY totals DESC LIMIT 5;
sgx___, if i write … “mangé” and if i have “mangé” in database … with my ereg_replace function … “mangé” in text field will be transform in “mange”
sgx___, it’s clear ?
Surely there’s some collation that will compare those accented characters as the same as the non-accented characters.
snoyes, thanks a lot. That did the job perfectly.
I am going to be migrating all databases from one server host to another. To migrate the mysql users and passwords as well as privileges I should just be able to dump and import the mysql and information schema databases correct?
If it’s the same version, you can do that for mysql. No need for information_schema; it’s a virtual database and will be created on the fly in the new server.
It will be migrating from 5.0.37 to 5.0.44
will that be ok?
Probably
ok…well I guess I will soon find out
thanks!
SELECT @myLeft := lft, @myRight := rgt, @myWidth := I have not seen @ or := in use before. could someone tell me what these are called so i might find them in the docs or enlighten me as to their usage? thanks.
!man :=
see http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
thank you snoyes (as usual)
It’s like plsql in Oracle, from MySQL’s procedural support.
uhm im sorry thats not very helpful but thanks anyway
or more like sqlplus, which contains even more fluff.
so := and = can be used interchangably?
No.
in a SET statement, yes. In SELECT, no
It’s a different language, beyond SQL.
ok
cheers guys.
:= and = are assignment in SET. = is comparision in SELECT.
whats the best ever mysql book to purchase i need to read this stuff on teh train. reading the docs “at work” isnt beneficial hehe
book
http://www.kitebird.com/mysql-book
The Book
Great for both complete newbies and experts. Rare combination
ace.
i love you guys. /puts 12th empty pint jug on table
ok bye!
thanks again
You’ll notice some fairly significant differences between the language you can use in mysql (the command line client) and SQL sent directly to the server via standard APIs. From a standards point of view, there’s foundation support which includes raw SELECT, UDPATE, INSERT, etc. Then there is
procedural (for stored modules) which supports additional constructs/syntax. Then for each database you’ll find something like…
the mysql command line client (such as oracle’s sqlplus) that supports an even different language.
if I just have a bunch of key-text pairs, is it faster to use a MySQL DB or just make a directory with one file per key?
How many?
:= isn’t normally supoprted in the foundation level of SQL, for SET or otherwise. So you have to be careful which context you are working.
luke-jr_work: It depends on what are you going to do with that
?
haptiK has quit
General information.
depends on what are you going to do
just lookup text by its key
luke-jr_work: And how many possible keys are you going to have?
about 59k
luke-jr_work: and will you be adding/deleting/updating the data?
no
luke-jr_work: How do you feel about having 59k files in a single directory?
the data is mostly static; changes are done by humans manually
fine?
luke-jr_work: I recommend a Wordperfect document, using MS Comic fonts.
…
luke-jr_work, its what databases do, use an index on the key as well
salle w2k gets sick at 5k
ping?
luke-jr_work: Did you ever try having so many files in a single directory? Every filesystem slows down a lot. In a database host (not only MySQL) 56k rows will fit into single table and that’s not big number at all for db’s
’sup?
trying it now
with ext3
luke-jr_work: ls will take several seconds ..
I don’t need to ls
cat 99999 is immediate
ext3 has an option to make it faster to open a single file from a large directory
luke-jr_work: ls is faster operation you know But that’s irrelevant
5 [Note] Plugin ‘InnoDB’ disabled by command line option when I try to mysql_install_db –user=mysql –datadir=/ux0/mysql –defaults-extra-file=my.cnf with various innodb parameters set in the
what options must I include in order to enable innodb?
luke-jr_work: If you are always searching by key with 56k rows database has and advantage. SELECT my_text FROM tbl WHERE my_key=1234; -
better use my.cnf for that purpose
Get one of the example .cnf files and edit it according to your needs
which ones error?
what’s the advantage?
luke-jr_work: compared to what?
which parameters? I don’t how to get it to tell me
cat 1234
s/how/know how/
mmm 2 secs I might be able to find a list
luke-jr_work: Who says there is advantage?
I’m asking which is faster/more efficient
luke-jr_work, the databases , use an index on the key as well
luke-jr_work: It’s up to you to define what do you mean as “faster” and “efficient”
anyone to help me in replication ,
?
luke-jr_work: If you are goign to do that manually at the console. cat 123 could be more “efficient” simply because you don’t need to run MySQL at all for that
i just used the mysql hosting doc to set up the replication
nothing work …
What exactly is “nothing” and how does it “work”?
from Java, I think
innodb_data_file_path, innodb_data_home_dir, innodb_flush_method, innodb_log_arch_dir, innodb_log_group_home_dir
when i make a change on the master db, the slave isn’t synced
thanks, let me try those
pastebin the results of SHOW SLAVE STATUS\G (on the slave server)
don’t forget to set innodb_buffer_pool and other memory options
yup
Just get one of the example .cnf files and edit it according to your needs
k
yeah, I have. let me try leith’s suggestion and I’ll get back
http://paste.dunnewind.net/235
Actually the only mandatory option is to turn innodb engine on if it is disabled by default. have_innodb that is
There you see exactly why.
Slave_SQL_Running: No
And the error is quite explicit.
i did a start slave …
You either didn’t start with identical data or you have writing clients connected to teh Slave
There’s a duplicate key error. Figure out what to do about that (skip the insert? Delete the row? Reimport the data from master?), and then restart the slave.
ok …
Just starting teh slave is not enough. Did you follow all the steps from http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html ?
Just starting teh slave is not enough. Did you follow all the steps from a href=”http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html”http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html/a ?
the problem duncanh is having is with the new 5.1 versions where we stripped all of the required defaults out of mysqld.cc to make innodb fully pluggable
I know. That’s why I suggest example .cnf files as they have lot of useful comments there
yes , i did all steps
all that happens is that the Slave is trying to insert row which is already there. You have to investigate and find why
they actually don’t include all the requirements
well, they miss one
flush method..
does a federated table stay synced with the source automatically?
if I have a query combining multiple tables.. is there a way to have this query create a new table in my database?
In a sense. Federated tables grab the data they need at query time, so it’s not synched in a replication sense.
i’m removing all the slave db
Which should be considered a bug and fixed
Yes, you can CREATE TABLE newTable SELECT… or you can create a VIEW, if that’s more appropriate to the situation.
well, maybe. the required variables should be fixed within the plugins though, which is on the plugin developer’s backs now
I’m doing some research on how to speed up already optimized SQL queries. i ran across setting global key buffers… anyone know about this?
snoyes, I think I want to create a permanant table, as all of my queries would go against this query’s results.
there’s no definition for innodb_log_arch_dir in http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html. what should it contain/what does it mean?
duncanh, it doesn’t do anything, and hence was removed
just set it to the same as the log group home directory
indeed, does bugger all, but innodb still seems to require it for some reason
And should the output change as the original tables change, or stay fixed based on when you create the table?
bAnd should the output change as the original tables change, or stay fixed based on when you create the table?/b
Why is the binlog full of /*!*/ comments?
but if you update the federated table does mysql then execute that update on the source?
yes
it’s the delimiter
You might think of a federated table like a VIEW, but on a remote server.
set by mysqlbinlog
I see. Why?
ok thats what i was thinking, now if the remote database is mssql is there an odbc or unixODBC engine that can be used?
no idea why they picked that particular sequence
I mean, why does mysqlbinlog need a different delimiter/
I think that’s under development.
this is the diff of my.cnf vs the innodb-heavy-4g: http://www.pastebin.ca/614403
worcester:/ux0/mysql # mysql_install_db –user=mysql –datadir=/ux0/mysql
Installing MySQL system tables…
2 [Note] Plugin ‘InnoDB’ disabled by command line
2 [Warning] /usr/local/libexec/mysqld: unknown option
2 [ERROR] Unknown/unsupported table type:
2 [ERROR]
you still didn’t set all of them
snoyes, how long does mysql cache federated tables? do they expire or is it refreshed every query?
innodb_flush_method
doh, sorry. I’m dumb. tried it now, and same result
snoyes, my concern is that the mysql server is going to have a heavy load and can cope with it, but the mssql server can’t, so I’d like to basically only hit the mysql database and then pass updates between the 2 servers such that it won’t overload the mssql server
snoyes- the original tables are not going to change at all. so its not something I need to worry about.
how did you set it?
fdatasync
mmmm
hey guys, in a java app I’m doing a rs.getString(“column”) on a field of type TEXT, but for some reason it returns null even though there is data in the column… suggestions/ideas ?
nkbreau, let me look
So I have a question, but I’m not exactly sure how to word it…
Here is the situation:
I have two tables, one for items, one for items+countries.
EricHerman, found the issue, nevermind lookin into it.. thanks for the help.
I want to select from the items table any item that is either in no countries, or in a specified country
SELECT * FROM Items WHERE …0 countries or country = UK in Item_Countries..
Can anyone help me/understand what I am talking abuot?
sounds like you need a left join and then check join column = null
nkbreau, good, I was just about to paste-bin a working example
Do you have any website that could explain left join to me? I have never used it
maybe http://www.varlena.com/varlena/GeneralBits/56.php ?
paste
pastebin
scroll down to the join basics part
so does this look right?
SELECT * FROM ads LEFT JOIN ad_countries ON ads.aid = ad_countries.aid WHERE country = NULL OR country = MY_COUTNRY
Yes, assuming MY_COUNTRY is specified properly ‘my country’ or otherwise.
could I just do …LEFT JOIN ad_countries USING (aid)… ?
and country is a field of ad_countries.
Yes.
There is no cache, as far as I know.
does country = NULL work? shouldnt that be country IS NULL ?
xzilla, IS NULL, yes
Doesn’t sound like federated is what you really want then (if you’re not careful, federated can create a really heavy load, because of the number of rows it has to pass around to satisfy the queries)
For example, if you do SELECT * FROM localTable JOIN federatedTable USING (someField); the remote table has to send back every single row in the table, so they can be compared to the local table.
if i run a bitwise search/test thing, will having the rows indexed still be helpful?
“SELECT * FROM table WHERE thing & 2″ or something
nope, no index used there
what software is the_wench running?
so if i’m expecting to have a lot of rows, I would be better off splitting up the states into columns of TINYINT(1)’s ?
hi. when i try to connect to my local mysql everything is “lightning”, but when i connect to another machine in my local network it takes ages (oh well.. 5 seconds?) before it “connects” (when its connected its fast though). ive been trying from another machine (linux) to connect and that one
doesnt seem to have any trouble.
i try to connect via mysql query browser
and when i use php i have the same problem, it’s slow “connecting” but when its connected everything run smooth.
anyone who knows why? ive tried connecting straight to the dedicated ip hosting and to the hostname, i dont have any trouble looking up the hosts (no DNS problem).
win 30
ml2k, could be a reverse lookup problem (reverse lookup can be switched off)
yea, that was the problem
it didnt have any problems with my linux client, but my windows-client wasnt too happy
thanks
Aynone know if there is a way to use the source statement in a stored procedure?
seems like it doesnt work in a stored proc or a prepared statement.
I need a subquery to evaluate as now when it returns nothing … how do i do this
coalesce(now(),(select ts from recent_entries order by ts desc limit 1 )) ?
Hello all, does anyone here have much experience with Eventum running on a windows server?
#windows
SOURCE is a client command. Server doesn’t understand it.
well it *IS* a mysql product…
iratik, it’s not so much windows issues but Eventum issues. I’m having some large speed issues when it is attepmting to list tickes, open/create tickets, etc.
Shrews, ah thanks. I was thinking about outputting a create procedure statement to /tmp then using source to read it in, to get around the limitation on stored procs creating stored procs
I have Eventum on IIS6 on a Win2K3 server with PHP5 on our inside management server. I have a MySQL server in a dmz segment also running on windows2k3.
anything I can check on why the response times are slow for these pages in Eventum?
windows overload
adf323, slow query log
adf323, first thing I do is run my sql statements in the console. if they come back fast there, then I know it isnt a mysql issue and you would have to see if it is a php issue
would slow query log be on my mysql server or under eventum?
or maybe check task manager to see which process is spinning while it hangs
mysql server has a slow query log. Have to check if it is enabled though
snoyes had suggested I use a pipe to input a command to mysql, anyone know the proper way to do that?
speak a da debil.
dbname
ToeBee, thanks thats kinda what he gave me..let me try
brb
hmm ok that works brp trying something else
how can i tell the size of my innodb table space? I know i can see Innodb free space in show table status, but i need the total size too, not just the free space…
an ls on the filesystem?
although there probably is a way to do it in mysql…
sure, but remotely
yeah, that would be nice
a few notes on my issue. 1) slow query log is not currently enabled. will see about getting that enabled. 2) neither system is getting any cpu/memory/etc usage spikes. (both systems are dual xeon dual core 3.2GHz systems with at least 2GB RAM) No appreicable network traffic either.
3) it taks ~ 14 seconds to change from the list view of a project with 1 open ticket to another project that has 11 open tickets.
adf323, check reverse dns (mysqld checks it on each connection) can be switched off
archivist, wow! That made a massive change! I didn’t modify our MySQL config but put an entry in the hosts file for the IP that Eventum was connecting as. Once I did this, the 14 seconds dropped to 4 seconds!
hello people
http://pastebin.ca/614515
just for review, i am still working on it
(select name from surveytag) union distinct (select name from usertag); how do i modify this to include a boolean column for surveytag.survey = 1?
select name, survey = 1 from surverytag
but then i can’t union it
because select name from usertag only has 1 column
so select some relevant column (or some dummy value) in the second part.
select name, 0 from usertag or something
but then i will get rows that don’t have distinct names
because some sureytag rows will have a survey equal to 1
that is my dilemma
In that case, which one do you want?
the first table
hey guys… can anyone recomend some good resources on relational database deesign patterns?
get a book on normalization
true, your design should reflect your data and how its going to be used
your pattern should have tables, fields, and keys
(select name, survey = 1 from surveytag) union (SELECT name, 0 FROM usertag LEFT JOIN surveytag USING (name) WHERE surverytag.name IS NULL)
name is ambiguous. prepending a table name gives me an error at `.name`
where did you put the table prefix?
using (surveytag.name)
oh
you have surveytag.name misspelled
sorry
hi all
fixing the spelling didn’t fix the error
i’m on mysql 4 btw
maybe using isn’t suppored?
supported?
i’m searching some infos about mysql, because i’ve a realy strange troubles, i think that mysql execute a subquery for every row of a table and not once for a select
you need to put the table in the select list, not the USINg. SELECT usertag.name, 0 FROM usertag… USING (name)
oh right
SELECT tipo_op, microchip FROM import_xml WHERE id_import=4 AND microchip IN ( SELECT microchip FROM import_xml WHERE id_import=4 GROUP BY microchip HAVING COUNT(microchip)1) Order by Microchip, Tipo_OP DESC;
Move the subquery to the FROM list, making it a derived table.
….FROM import_xml JOIN (SELECT micro….) AS derivedTable USING (microchip)
in i run it on mysql i must wait about 30 mins for results but if i use it under ACCESS i get an istant result!
snoyes i still get duplicate names
is it possible to run mysql on a read-only filesystem, if i dont plan to do any writes?
snoyes hi
howdy
do you work with mondrian or did you know mondrian’s developer in some mysql conf?
Then they both come from surveytag.
mmm but it isn’t same way
oh its because
and why do it run the subquery for every row of the table?
the first table has varying survey values
that joins with the second table which has 0 rows (desirable)
I imagine it has to do with the possibility of a correlated subquery appearing in the WHERE clause.
is it possible to get the output of “show full processlist” using a select query?
Alumin, i don’t think so, why?
I’m not familiar with mondrian.
ie, so I can choose which columns I want and filter certain processes using where clauses, etc
so my last problem is how do i select rows with distinct names from select name, survey = 1 from surveytag?
it is
snoyes tks
i need the result of subquery to filter the main query
Alumin, ah like that; no, i don’t think so – maybe with a subquery?
Yes, but I think it’s a 5.1 thing
perhaps…I don’t really know what a subquery is yet, but perhaps that’s the answer
but it’s a bit strange that mysql runs the subquery for every row
yeah, you’ll need 5.1.7 to do that.
hmm, ok…we’re still 5.0 here but I’ll keep that in mind
thanks for the info
Alumin, hmm subquery doesn’t work
If the query were SELECT * FROM table WHERE id IN (SELECT * FROM otherTable WHERE table.x = otherTable.y); it would have to.
is there a way to run a database from another location than the other databases on a server?
You’d have to decide which row to pick from surveytag.
name
the query is: SELECT tipo_op, microchip FROM import_xml WHERE id_import=4 AND microchip IN ( SELECT microchip FROM import_xml WHERE id_import=4 GROUP BY microchip HAVING COUNT(microchip)1 ) ORDER BY microchip, tipo_op DESC ;
got disconnected – did that last post make it, re. MAX(survey = 1) ?
!m fzzzt federated
fzzzt see http://dev.mysql.com/doc/refman/5.0/en/federated-description.html
distinct names is what i want
MySQL transforms subqueries in an IN() clause into correlated queries. See http://dev.mysql.com/doc/refman/5.0/en/in-subquery-optimization.html
SELECT name, MAX(survey = 1) FROM surveytag GROUP BY name
thanky
hrm i’d have to run 2 mysqlds w/federated
complete and utter ownage
snoyes_ you are most generous
SELECT name, MAX(survey) FROM (SELECT name, survey = 1 AS survey FROM surveytag UNION SELECT name, 0 FROM usertag) AS dt GROUP BY name; would cut it, I think
where’s the source for 5.0.45? i only see 5.0.41 on the GA downloads page
yeah i got that
i can at least put it all together my self
Good question. Looks like you can pick a link and change the URL to .45 before selecting a mirror.
k, thanks
hmm 5.1 could do it…
mysql -B -N -e “call t1()” | sed s/\\\\n/\\n/g | mysql
is there an easy way to reset an encrypted password manually in mysql?
It’s just like updating any other field (unless you mean the password of a mysql user, in which case use the SET PASSWORD command)
snoyes thanks. I went ahead and rmoved the \\n’s directly
but it still doesnt work
ah but I didnt try the -N
brb
damn
-N is the short form of –skip-column-names
aha innodb lets you specify the location, whew
snoyes, ah ok thanks
ok I had gone ahead and moved all of the \\n’s and it still doesnt create the stored proc
i am thinking maybe you can only run one command via the pipe or something
the passwords in my u_password field look like *70EB20A11F7DCF2A7A3F8816F8943F535271B56E
instead of a legible passwod
word
nope thats not it I just tested, I can run many commands
hy there
hi ao
I was able to use your code just as written, plus the sed I posted, and it works.
snoyes, did it create the stored proc?
yes
ok i am going to modify and try…
brb
snoyes!
woa
aoirthoir!
it worked
how would i compare a date to one week before NOW(). Like i want to select all rows, that have a date field older than 1 week from today?
ok i understand the awk stuff to remove the \\n’s
but when I had removed them it still didnt work (when I did it prior)
so any idea what was the cause of it not working?
SELECT * FROM table WHERE dateField + INTERVAL 7 DAY NOW()
snoyes, thanks
oh and the snoyes! part was excitement not yelling.
With everything on one line, the DELIMITER doesn’t work right (since the delimiter takes the rest of the line)
ahhhhh
I have a question related to mysql … indirecty … I have a Mysql 5 DB which on I can connect from a distant place thru ODBC (with Access ), or directy with mysql query browser, … but … and there is my question: My DB data are “latin1″ (charset: iso8859-1) and none the way I connect
chows me my data in the correct charset … does anyone have a clue ?
so I have to have it on the same line then remove the \n’s
well, replace the ‘\n’ characters with a new line character
I tried to found charset options in both tolls I use .. but can’t find any
snoyes, aye. I just ran the query without your thing and then I realized duh…the mysql wasnt seeing \n as a newline it was seing the literal `\n`
thank you kindly for the help
You’re welcome.
i have to look it up again but there is a website you can go to where people prepay
snoyes, do you know if the order of operations would make “select * from table where dateField + interval 7 day now() and foo=bar” does what i think it would?
and they can call and it charges whatever you want
they keep some of it and you keep the rest
Should be ok, the and = are higher precedence than AND.
so if you ever set that up I can pay you for all this help that way or via paypal (i know you arent asking for pay but you are very helpful and I am willing to pay you since my requests arent super simple)
snoyes, great!
I don’t have the capabilities to change that DB charset, cause my client don’t xwant
It’s kind of you to offer.
its more kind of you to offer the help you do G. My employer I know appreciates it (I let them know) so any time you want a to do something complicated I ask for dough, let us know.
mmm…cookie dough…
& milk
quit work, get fat
hehe
i was just discussing cookies and milk with someone
wierd
ao since your name is so close to mine
i insist you change it..nay I demand you change it
or not
and I mean it.
contraction of aotorisan
ah neat whats that?
better ?
lol
kind a … mmm … i’m french … herr, don’t have the bird name
what’s wrong with this http://pastebin.ca/614587 (other than the lack of a semicolon at the end)?
I was acutally just kidding
but it’s a tiny blue bird
what’s wrong
Don’t ask us “What’s wrong with this query…”. We are not SQL parsers. We do not care to look character by character looking for errors when MySQL will tell all of us WHERE the error is. Paste the FULL error issued by MySQL.
heh – INT() is a data type, not a function call.
lol
anyhow ao i am silly a lot of times. especially when snoyes helps me solve a complex problem
^^
so, id INT, or id INT(someNumberGoesHere), but not id INT()
thanks.
and TEXT columns can’t take a default vaule.
not even ”
background = TEXT() NOT NULL default ”,
is that supported too ?
askingcause of the equal ?
good catch. no =
maybe hav’ found my answer … but f*** i have to reboot that damn windows for it
c ya
awesome
it only took me a month
but i have recovered my linux box completely!
aw crap
maybe not so much
i know how to set a comment on a column with COMMENT, however is there a way to grab just the comment for the column? right now i am using ‘SHOW FULL COLUMNS FROM tbl’
blobaugh|ct, yes one sec
blobaugh|ct, select column_comment from information_schema.columns where table_schema=”dbname” and table_name=”tablename”;
aoirthoir, you are just a SQL genius, you sicko!
lol erstazi
any reason i wouldnt be able to acess information_schema? says it doesnt exist
erstazi, i have started to work a lot with information_schema. I use it when I want to create things like triggers set NEW.col1=col1 for 50 columns is a pain, but with a stored proc I can create that code autoamtically for all columns in a table
blobaugh|ct, are you using shared hosting?
blobaugh|ct, which version of mysql are you using? select version();
old version
erstazi, its probably the version, all users can access information_schema
pretty sure all of them can
dedicated server, we use plesk too if that matters
blobaugh|ct, yeah, check your mysql version
version is 4.1.20
blobaugh|ct thats why
plesk is only helpful to a point
mysql 4 doesnt have information_schema
I hear the cringe of upgrading,… I know the feeling
how do i limit connections to my mysql server by ip?
im pretty sure its a version 5 addition (is that correct archivist ?
so phpmyadmin is getting it. it is probably parsing show full column though huh
yup
blobaugh|ct, yes.
bummer
or show create table
how do i add another ip to the rule?
from mysql4 to mysql5 there is such a huge jump, of course for the better
well we have a test server setup so we can begin migrating our apps to a newer system. hopefully that will happen soon
MitchM, CREATE USER ‘bla’@ ‘192.168.1.12′ IDENTIFIED BY ‘password’;
then set your permissions
MitchM, then a specific user can only access from certain ip addresses
of course replace 192.16.1.12 with whatever you want
aoirthoir, my connectino to mysql is being blocked by what would seem a port ‘daemon’ of some osrt.
MitchM, if your provider is limiting access to the mysql server from the localhost you will have to make some adjustments
3306
MitchM, is it from a hosting provider or on your own computer?
i get “ip” denied.
its a company server
MitchM, then its a question to direct to them.
I am that guy now.
everyone else took a timely vacation
MitchM, its your hosting company?
nay – its just _a_ company. that runs a mysql server.
try mysql console instead of telnet
mysql -u username -p -h ipaddress databasename
see if you can get in that way and if not copy and paste the error to http://www.pastebin.ca/
will do thx
Is AES_Encrypt/Decrypt something that has to be compiled in (5.0.27)?
aoirthoir, http://pastebin.ca/614643
MitchM, sorry I have to go for 20 minutes
erstazi, brb
Undernet? Is that you??
thats disconnectnet
netsplit
um am I alive still?
yup
ok whew
last time that happened I was talking to someone and it was to myself heh
:-]
Hello, I’m having an issue where I can’t use MYSQL_CLIENT_COMPRESS flag from PHP’s mysql when connecting to the server using tcp but I can use it when connecting through local socket/pipe. Any ideas?
I’ve got one table which contains fields referencing several other tables. How could I use a join to pull all that together? Or would it be better to just use the WHERE t1.id=t2.id, etc?
t1.id=t2.id is a join
I know, but should I use that rather than the LEFT JOIN/ON/USING method?
I want to script the changing of the root password. Is there a way to pass mysqladmin a pre-encrypted password?
how do i limit how long a query can run?
Can’t. Shouldn’t.
id, zipcode, streetname, streetsuffix, county, city, state, startAdd, endAdd . Each zipcode might have up to 3000-4000 streets, and the total table is 32 million lines. I have a primary index on id, and an index on zipcode. The following query is taking ages:
SELECT zipcode, COUNT(id) as numStreets FROM zip_st_table GROUP BY zipcode ORDER BY zipcode
Any ideas on how to speed it up?
(I am wanting to count the number of streets in a given zipcode)
How would I left outer join multiple tables to one table? It seems once I start doing more than one left outer join I run into problems because some column named in the USING list doesn’t exist in the other tables
Do I just list the main table last, or something?
er..
nm.
what i was trying to do doesn’t make sense..
wait.. it sorta does.. I just want to pull in information from other tables but still list records with null fields too
is there a way to do a mass find/replace in a table ?
multiple columns
!man replace(
see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
yeah .. i found that
and that’s why i came here to ask
mysqldump | sed or awk | mysql
ah .. the brute force method
You use ON instead of USING.
Is there an index on zipcode?
oh, you already said that. What does EXPLAIN show?
What if you replace COUNT(id) with COUNT(*) ?
snoyes- I’ll give the COUNT(*) a try in a bit. was afk for a bit, and gotta run something “more important” (imagine that- something more important than my project )
greetings, I am trying to get a timestamp from mysql, but i dont want to write any data to the db….is any way to echo the output of now() directly?
Kenman, SELECT NOW()
Habbie, thanks!
np
I use ‘create as select’ from a table which is readonly for me with SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. The table I create is in my own db and it is used just by me in one thread. Why I sometimes get ‘ERROR 1213 (40001) at line 359: Deadlock found when trying to get
lock; try restarting transaction’ message? Where the deadlock may occur?
create …select needs to set locks on the table being selected from if you are using binary logging
use binary logging? what does it mean. I am sorry for this question, this is not mine database, this is the wikipedia toolserver, so I need to understand how to workaround this problem
HarrisonF, can he do CREATE table LIKE othertable; INSERT into table select * from othertable; to avoid that problem?
oh shit, toolserver users
lol domas what’s that?
playground
dunno what that means
in some isolated network, which has subset of wikipedia databases replicated to it
well, probably turning locks unsafe for binlogging could help
I’m not sure if that makes it not set locks on create .. select ..
I cannot tune locks on the public db, it is readonly for me. My concern was TRANSACTION ISOLATION LEVEL READ UNCOMMITED resolves such issues. Am I wrong?
so, am I right the solution does not exist?
mashiah, you are trying to create one table from another
and you want the other table to have the same data as the original table?
is this correct?
HuggyBear, I used to live in cinci, over there in Hyde Park.
off of duck creeek.
no, I am doing some selection, I can paste the query here in a minute
paste the query into http://www.pastebin.ca/ along with the results.
Is it possibel to convert mysql 4 databases to mysql 5 directly?
vaq best dump and reload
see upgrading docs for issues
Just dump from 4 and import on five?
yes
Thanks.
you may get app errors from some joins
done
mashiah, ok paste the link in here that pastebin.ca gave you
wb salle.
How about the difference between table setup from 4 to five? Ain’t the timestamp format different?
http://www.pastebin.ca/614820
the readonly db name is ruwiki_p, other tables are in my own db and nobody accesses it except for me
i lieve very close to there
*live
ERROR 1064 (42000) at line 4163: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘read timestamp(14) NOT NULL,
vaq to is a reserved word you could try `to`
when dumping to 5 from 4
HuggyBear, i noticed from your ISP I used to have road runner myself.
clifton heights
mashiah, looking
lol… masquerade… lol
vaq edit those lines to 5 standard
HuggyBear, ah
?
mashiah, does the select statement work when you are not doing it as part of a create as ?
no, lack there of…
not sure, the output is too big to try, maybe I will try redirecting to a file. But the problem is that I cannot predict when the error will be, sometimes everything is ok and sometimes not
the uncommitted isolation level could probably work, but..
I use ‘SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;’
http://www.pastebin.ca/614831
I dont know if that will work or not mashiah …also you might just try a temporary table
like this:
http://www.pastebin.ca/614834
also, i typed table lal i didnt realize it was la1
so adjust accordingly
tried without table creation, the result is the same
mashiah, ok then it is not a result of the table create as (sometimes I get errors with that so I went to create table like)
but our project manager is here now and I have to give him an update
so ima be back in a bit, sorry G
ok thx
does anybody know why my scripts not take 4 times slower since I converted from myisan to innodb ?
You have to pay extra for the slow option.
a I thought it was that
seroiusly though, moving to innodb has become 4 times slower
did you forget to oil it
I just gave it a bit of spit and polish
I thought you said “not 4 times slower”
i don’t think he used enough cabbage
agore time to use explain, and other methods to find whats actually slower
Provide more detail, like an explicit example of SQL that behaves poorly.
sorry, ment to say 4 times slower
could I paste my.cnf some place, I think Ive got a setting wrong in that
ucould I paste my.cnf some place, I think Ive got a setting wrong in that/u
A pretty active storm is coming.
pastebin
thanks
hello
select * from Allocation_Private where NetworkAddress NOT LIKE ‘10%’;
is there a way I can only print unique entires ?
distinct
my first column is an ID, and I only want it to print once.
I have a “user” table, an “access” table to grant privileges, and an “audit” table to track last-logged-in datetime… I’m running a few left joins to bring all the data together…. but if a user has never logged in, there’s no “audit” to join against them in the user table, and they’re lost
from the report. —- I don’t need a huge amount of hand-holding, but can anybody suggest how I could improve my query? — http://pastebin.ca/614871
as a followup, i could split it into two queries, i’d just much rather not.
are there stats on how many websites (linux and windows) use mysql vs microsoft sql vs postgresql?
if so where would they be?
thats my my.cnf file
/var/lib/mysql is mouted on a raid 10….the rest should be on raid 1
Hi guys, I would like to have more information on combined selects (where I select in one table, and compare that to values from another table) in one query…
or archivist who ever posted me paste bin
I have a table with a column seller_id, where seller_id can appear multiple times in that table. What’s the best way to select a list of each unique seller_id in the table, and a count of how many times it appears in a row?
agore thats not likely to give you 4 times slower look for the real problems, actual slow queries, bottlenecks, missing indexes
its the same code archivist that I ran on myisam tables
code yes idexes the same?
archivist yeah
I run this batch job every night
normally takes two hours
coverted all the tables to innodb last night….added in the innodb lines to my.cnf that I pasted before….
now its on course for 8 hours at current speeds
Greetings
quick question
what’s wrong with this query?
INSERT INTO imagelibrary (`img_filename`, `img_cat`, `img_date`) VALUES(‘1leftarrow.png’,1, NOW()),(‘1rightarrow.png’,1, NOW())
what /is/ wrong with that query?
yes
heh “ ”
nop
in that, what causes you to think that there is something wrong with it?
what does the server say
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1(Error Code: 1064)
;
nope
Did you find one of the queries that has changed behavior the worst?
the only changes to the code are START TRANSACTION and COMMIT
You’re not understanding.
no, no queries in particular
You report 4 times slower behavior. Since many of the queries probably aren’t different between the two engines, you must have a hand full of queries that behave very badly now, that used to behave much better. Find one or two of them and post them.
I ran one of our batch jobs this morning that took 2hours instead of 30 minutes and that was just a simple query in a loop
Pick one or two, if you’ve tested and found them all much worse. Be sure you have verified the difference.
A query in a loop??? Why?
Fenix|lappy, well a copy paste into my box , works for me
I need to caclulate the value to update it with
archivist, I’m looking at the whole query, I have an error… one too many opening (
Im convinced ive setup innodb settings wrong in my.cnf
I have a feeling we’ll speed this up by an order of magnitude or more, unrelated to engine type, if you provide sufficient information.
That could be. There are many InnoDB specific settings.
oh it’s that time already , eh ?
yeah, and there the only things ive changed
Your description of SQL in a loop is interesting. I bet there might be significant improvement to be made there.
possibly, but its the same for all my code routines that I run
its like im not using memory buffers or something
I set my innodb buffer pool to be 4GB
maybe its too big ?
do you *have* 4GB ?
Got 8Gb
64bit Mysql
and you only have inno tables ?
yeah, and some emory tables I use for loggign
memory*
how much memory it’s using is easily showed
or eyeballed via phpmyadmin
When you’re done configuring, you might want to ask about your loops. Trust me. That could be a much more significant issue. You could end up with a couple of minutes (or less) runtime and not 30 minutes.
Hi jaypipes
hi there!
is there a way to disable/change location of the “test file” mysqld creates when its launched?
oh it’s just a warning
im just wondering if you can have a 4Gb innodb buffer pool on a 64bit system ?
you can
i have a 14GB innodb buffer
ours is 32gb
works like a charm
well, I think your question is answered!
it works like a buffer
ah, sarcastic as ever, Eric. 
hi jay
how’s it goin’?
lonely, jeremy is travelling
jaypipes, can I ask you a question about the webinar this morning?
shoot.
lonely without your lover?
haha
jaypipes, about the partitioning – IIRC, during the webinar, you called it vertical, but the slides you sent me say horiz.
what’s funny is that I probably talk to him more than he talks to his wife
yeah, typo in those slides… it is vertical.
As I pointed out at the UC!
horizontal partitioning is when you have similar table structures that contain shards of a larger data set
indeed!
jaypipes, NP, thanks!
jaypipes, OK – can you give me an example of vertical then?
Shrews, I didn’t see him at the UC.
good article here http://dev.mysql.com/tech-resources/articles/performance-partitioning.html
Shrews, thanks!
the example in the webinar today was vertical partitioning: where you split a large table into smaller tables for specific reasons — mostly to deal with Query Cache thrashing, InnoDB SELECT COUNT(*) woes and scenarios where memory is at a premium and you only waant in memory those field that
are most frequently accessed.
I need to fix my irc bouncer
also, you might vertically partition a table to have part of the table in InnoDB and another part in MyISAM for FULLTEXT indexing needs.
scooby2 just curious – have you ever measured performance wins by going from 4 – 8 – 16 – 32 GB cache ?
jaypipes, Ok, the partitioning fog is clearing. I take it you could use both types and have the tables on different servers?
ujaypipes, Ok, the partitioning fog is clearing. I take it you could use both types and have the tables on different servers?/u
absolutely.
to the usage of both, not your need to spend time on dev.mysql.com
ebergen, do you live with JC yet?!
also check out MySQL Forge for good stuff: http://forge.mysql.com
yeah. 32 was better then giving it all the ram
or less
for our app
I added the SQL script for finding the top 10 worst performing indexes: http://forge.mysql.com/snippets/view.php?id=85
jaypipes, thanks. I took it to mean vert/horiz – dev.mysql.com is important too!
jaypipes, I’ve looked at the forge too.
scooby2 huh ? I asked about the opposite – increasing the cache
except, of course, in the case of the MySQL Query Cache… where a bigger size can actually lead to poorer performance.
really ?
If I have a master and a slave and want to add a second slave, but *don’t* have a mysqldump from the master and don’t want to mysqldump the master (because it will make the app unresponsive for a while),
so why doesn’t the optimiser detect and prevent it ?
yeah. one sec… fetching Peter’s article.
how can I start the second slave using a dump from the first slave?
a badly designed/sized cache can be a killer as I learnt once upon a time
the first slave’s dump has master data pointing to its own binlogs, whereas I need the position in the master’s binlogs
archivist how can a cache be designed? isn’t it filled on a need-by-need basis ?
http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/
okay, thanks
adaptr, yes but cache replacement can be worse
adaptr, mine was 1 block too small
jaypipes, interesting thing about partitioning, I don’t remember that topic on the CMDBA exam.
seems rather important actually.
archivist doesn’t mysql work with pages ?
never seen the exam…
it actually can. especially if you have other non innodb stuff going on and the thing starts swapping on you
InnoDB works with 16K pages arranged in 64M extents. MyISAM, however, does not. It has no record cache and relies on the OS buffering for record data.
correct
jaypipes, I thought all the mysql.com employees had to take the exams.
jaypipes ah this would be reason #1 to use inno if you want reliable performance, then
and PBXT too does paging I believe. Falcon, I have to dig into the docs…
adaptr, this was in a graphics program some years ago but the last used invalidation was needed EVERY block
archivist that should be caught by a cache optimiser, surely ?
HarrisonF no, I’m pretty sure adrienne wouldn’t appreciate me stumbling into the house at 2am after being at the bar all night
it would be trivial to record the amount of replacement needed for every cache, and set it off against the current cache size
not necessarily. InnoDB great for certain things (like high concurrency and lots of mixed reads/writes and UPDATEs). MyISAM is really great for large datawarehouse environments with little write activity (except for the ETL work) because the size of the datafiles are MUCH smaller than InnoDB
for the same number of rows.
jaypipes well, if that last translates directly to fewer IO, then yeah
I want to see partitioning support auto packing myisam on rotation
but in a nice way so it doesn’t crush the box
but, then again, InnoDB is really good for single primary key lookups and ranges, b/c of the clustered index layout… so, it totally depends on your needs and the ways you access your data..
is there a worklog task for that, by chance, that you know of?
or a bug/feature request?
jaypipes still, wouldn’t the cubing for the warehouse take more time on ISAM ? or can you cube it on inno and copy the tables to ISAM for storage ?
AFAIK, the cubing happens outside of the storage engine, but I may be mistaken on that…
I mean, as long as you end up with less storage in the morning, and not too much load at night
right.
oh well, /me not a native on mysql….
no worries, mate! I’m not an expert by any means.
oh I see – yes, the cubing doesn’t happen anywhere near the storage engine, but the engine chosen will always directly affect disk IO and sundry speed limits
jaypipes no, I just thought of it
yes, that’s right. For MyISAM in datawarehousing, performance is dependent on having solid indexes set up on the tables so that the lack of a record cache doesn’t come into play.
so if one was able to cube/crunch/churn/compact/whateveryoucallit on inno tables (speed), yet then copy the tables to isam (small and still fast for reading) that might mean smaller storage requirements
and still acceptable lookup performance
might be good to add a feature request…
possible, but not sure what the need would be for InnoDB in a datawarehousing envirnoment, since you aren’t concerned with concurrency, updates, transactions, etc..
jaypipes of course, index RAM *is* of critical importance… no idea whether isam indexes are smaller or larger than inno indexes
smaller.
and very differently designed.
jaypipes I did not say use inno IN DWH, but it will potentially cube much faster than ISAM, so just bulk it to ISAM when it’s done cubing
Any clue as to when the 5.0 doxygen tree is going to be merged to the 5.0 tree?
MyISAM indexes are B-tree indexes, arranged in 1K blocks and are all secondary, non-clustered indexes. InnoDB uses 16K index pages and clusters on the primary key, with all other indexes as secondary indexes built on top of the main clustered B+-tree index. InnoDB also has an dapative hash
index for PK lookups, but you have no control over it.
