Good morning all We recently upgraded from MySQL 5022-log to MySQL 5044-log on a CentOS server We run a service
indeed i did read it.. even though it means little to me as it was working before and i do not know of changing any settings and it therefore seacing to work
'expire_logs_days=10', remove or comment out this crap out of your bloody my.cnf, Sniper_jo
how about the 2nd line ffs?
this is an unintended side effect of the progress in linux distros. anyone can install powerfull server processes, with no idea of how to manage or secure them.
and the the 4th?
blah.. ok i was going to try and keep the contents of my databases
but who cares…
adaptr, ah, yes, the good ol' times….
Sniper_jo, are you in write-only mode? omfg
Cheers for the advice
what have your databases got to do with anything over the last half hour ??
[ended] is a hint. Now that you know the problem, you're giving up??
Get out and have fun.. before the next fedora linux distro comes out..
sad little fscktard
he's been bullied by the smarties!
grow UP
indeed i have… hence i am leaving..
get a life, dude
Strange. That's not a good sign when you lead him to the finish line and he doesn't recognize it.
it's circumstancial evidence of Win-think
well if you were perhaps a little more helpfull i may of surpassed the finnishline
o_O, I step away for 10 minutes and this is what happens?
this kid thinks he can be clever.
you got your answers ! how DARE you claim you were not helped, you ruthless little fucktard
/o\
settle down a bit, it doesn't have any bearing on any of us
let alone non-paid help
i diddnt claim i wasnt helped.. would just be good if it wasnt in riddles
of course not, but this back-biting is reminiscent of a 6-year-old
volunteer is the word I was looking for
as far as I know, the helpers did their best to figure out what your problem was.
Yes, so quietly ignore it please
grumble…
guys, lets move on..
is that the way you talk to 6-year-olds?
inddeed… to reinstalling mysql
you vicious old madman
I don't know - I don't know any
adaptr, I do, they will take over the world
prolly, yes
with their cellphones and their interwebs and their chauffeured cars
hah
erstazi has been watching too many Kids Next Door episodes imo
we'm too old now - can't take the strain anymore
can't keep up with the MTVs
inviso, nah, just got kids of my own and step kids
they are evil
oh you meant *eventually* - hah, hard to argue that
heh
ciao ciao
Sniper_jo, auf wiedersehen
i hope xgc finds some form of happyness
even if it is infront of a computer screen
that's an odd statement.
Sniper_jo, leave while you can ffs
lol
ok i am gone
provoking us any more would not help your case.
what a fscking retard
inviso
inviso is a perl hating bastard
psh, not that silly
hehe
of all the people in #mysql, thats the first time I saw someone cause a fit
I mean super, kick him in the face powers
karma inviso
I have inviso with a karma of 3
we get 4-5 of those a day in #apache. adaptr and I are used to them
bot does not forget
thumbs, I attend #apache occasionally
well, not that obnoxious though… just clueless
karma archivist
awww, he doesn't love me though
thumbs, I must join when its quiet heh
yes. You must have never met pradalver
trailing space problem inviso
and setuid
karma archivist
I have archivist with a karma of 19
thumbs, yes I have, let me think…
ah
setuid ftw !
i must fix that
thumbs, what about that nick? it rings a bell
the only difference is that setuid thinks he knows what he's doing.
hello there anybody knows the channel for pevasive sql hosting or can help me a little about it ?
when in fact, he's misleading everyone.
he was so everything-less it wasn't even funny
*pervasive
Do you have some specific sql in mind?
anyhow
we have months and months of logs for your entertainment, if you wish.
thumbs, if I had months and months of time I would laugh about it heh
thumbs, I can imagine, I have seen many good flamers in my day that can start a flamewar over a penny
the worst are the ones that refuse to help you help them, and question the validity of your answers.
no actually i wanna know something basic … i got a windows 2000 with a programm on it which uses pervasive sql. Now the hd broke and i have a file backup from the system which i cant boot from. the programm got run once a week so the db shoudnt be corrup. Can i somehow retreve the data from
these files ?
thumbs, swimming in de-nile
so i can insert it into the new database or can i just copy some files ?
blubs, Pervasive PSQL is Pervasive Software's relational database (RDBMS) product
How is pervasive sql host related to MySQL?
blubs, http://www.pervasive.com/psql/
blubs, a href="http://www.pervasive.com/psql/"http://www.pervasive.com/psql//a
erstazi actually i just came here because u could probably know the channel where i can ask .. or anybody probabaly knows about it still
someone have enemy territory tc in kubuntu?
try #sql or try support directly with the vendor. It looks like a commercial product with commercial support as part of the purchase.
threnody, .ch != .fr (.fr is SniperJo)
Keep hitting this nasty bug with myisam+triggers+"for update" queries. Scripts were wrote for innodb, I changed table types and continually get 127 errors …
inviso, agreed
ok thank you
perror 127
!perror 127
Record-file is crashed
ouch
adaptr, threnody, heh
no, 43%
Take a look at http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html
Take a look at a href="http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html"http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html/a
heh
rounding error
my feet smell
sorry, I was thinking off the top of my head… I been very busy on my feet making reloads
come again?
thumbs, reloads of rifle ammunition
ok…
foo.sql', can I recover that "snapshot" as simply as 'sql -uroot -pwhatever foo.sql'?
hi guys, when i hit my website, i get "SQL/DB Error — [Field 'link_title' doesn't have a default value]" — I want to turn these errors off on the site. Is this a mysql setting or a php setting?
that's the basic idea
the command is "mysql", but yes
thanks folks - that will leave any other databases alone, right?
sounds like a php hosting problem
I typo'd…
i turned off both "display_errors" and "display_errors on startup
in php.ini
You can open the .sql file up and look at what it will do if you don't feel comfortable running it
and what is SQL/DB? Sounds like a php db library
Can't connect to local php mysql web hosting server through socket '/var/lib/mysql/mysql.sock'. Does anyone know why this might be happening?
sounds like it's not running
I have, and I feel more comfortable having done so, but I've avoided learning SQL for many years now. I just wanted to be sure.
Thanks a bunch!
np, good luck
how do you start it up?
/etc/init.d/mysql start or something similar to that
k, thx
or /etc/rc.d/rc.mysqld start
damn, neither of those exist
rc.local?
what linux distribution are you using?
I'm using centos
ls -l /etc/init.d | grep mysql
provided he even has an init.d directory.
If he doesn't, we'll know shortly
I have an init.d directory
but that command does nothing
Is it possible to not have one? I mean wouldn't you be running of a bare kernel otherwise?
sounds like mysql isn't even installed?
How did you install it?
I installed it using yum
you would have a BSD-style init structure then
i.e. /etc/rc.d/rc.*
Like on my slackware install. But essentially they are the same no?
as system root do 'service mysqld status'
slackware is different.
it's an unrecognized service
hehe for a second I thought I was in ##linux
yum list installed | grep mysql
heh.
you didn't install the server
it shows mysql.x86_64 and mysql.i386 as being installed
and mysql-libs
grrr, wtb man yum
as system root do 'yum install mysql-server'
thx
mysql-server is the package name? Saves me looking it up
as I recall
ah ha, here's the yum command I was looking for. yum search mysql
That should give you a rather long list of all available packages with the word mysql in them. Pick and choose as needed
you might find that the CentOS 4 yum install of mysql server creates the proper rc.d kill script links, but not the start script links. So when you reboot, the server might not automagically start. Remember that if it happens.
im still getting "SQL/DB Error — [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]" how do I turn off these errors?
a better solution would be to fix the broken query
^agreed
I'm getting the same error as Kev
i know but i dont want every error to be public
php only returns the mysql error if you code it to do so.
i turned off both "display_errors" and "display_errors on startup" in php.ini
then make sure your queries don't throw any errors
i.e., do input validation.
mysql_query() returns false if the query fails. The error is only show if you call mysql_error()
shown*
that's an error message from a db wrapper library
does mysql have a "strict mode" or something like that?
i thought i remember hearing about something like that
and i need to turn off strict mode or something
you need to look at the php code itself. Look for '…mysql_error(
you need to look at the php code itself. Look for '…mysql_error()…' in there somewhere.
Yeah just comment out mysql_error()
plus fix your query.
Anyone think they could help me out with my error e_e
You might want to keep the errors, maybe head over to php.net and learn how to print those errors to a file for later use.
i found this in my.ini
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
can i just comment out this line?
you're asking in the wrong channel.
… that's from mysql's ini file
doi
sorry
I'm getting the same error as kev but it's saying "MySQL server version for the right syntax to use near 'ORDER BY time ASC' at line 1:undefined"
Here's the query "SELECT * FROM public_chat WHERE timeNOW() - INTERVAL 30 SECOND AND map=".$_SESSION["map"]." ORDER BY time ASC"
http://dev.mysql.com/doc/refman/5.1/en/faqs-sql-modes.html
You'll need to figure out which mode is suitable for your queries.
ok thx
that could be same for henaro too
I'm not trying to make the errors not visible i
I'm trying to fix them
Well I'm assuming kev2 is running what he believes is a valid query …
I think I'm running a valid query
lol
But I'm an idiot.
henaro, what does $_session["map"] return?
It returns a number
Which is set on login to 1
so is ur map column a integer column?
ur?
Yeaah~
ok
It should be
well try this
Let me doublecheck
try a statement w/out the session stuff
just put in a number
"select bla bla AND map=1 order by time asc"
and see if that works
Okay
that why u know if it's a syntax problem or if ur session thing is giving u something bad
or maybe ur map column is not integer
Lol it got rid of one error and gave me a new one e_e
Heehee
fun fun
you're one step closer 
I have it set as a tinyint(3)
Yeah
any way for you to drop the instant messagign acronyms, kev2_ ?
lol
im out anyway, peace
that is one annoyig habit.
?
using instant messaging acronyms, i.e. 'u', 'ur'.
thumbs, Oh in 6th grade I had a devil typing teacher that would eat us if we typed like that.
Mrs. Bruno
She was scary. I think she made me cry once.
select 4175/4067.42866 — trying to get percentage of that
that gives me 1.0264
think the percentage would be 2.64
convert the values before dividing them
also, that is definitely 1.0xxxx
what do you mwant conver them before diving them ?
4175/4067.42866 the dot is decimal point how to get 2? you need more than 8000 to get 2
lost me
the result seems correct
4175/4067.42866 — what the percentage of that ?
do you know division?
yes
that result is correct
i thought i had to * 100 the result value
if you want percentage, divide by 100 again
sorry it is multiply 100
select 4175/4067.42866 * 100;
102.6447
so, that should be correct
that dont make snese
102 percent
102%
your value is bigger than the dividor
*divisor
4175 is 102.64% of 4067.4286 is correct. you're not thinking clearly
percentage does not have to be between 0% and 100%
wonder how my co worker got 2.6447
Pct_above_Avg
this isn't coming from MIT, eh?
using a different formula, maybe -100%
mit no
is the divisor the avg?
count/avg
above avg probably means (count -avg) / avg , thats above
trying to get Pct_above_vg
you need to define that term first
what does the first figure represent, and then the second figure.
count(4175) is the number for a given day, the other value is the avg click for a given month
tyring to get Pct_above_avg
well, that days' figure is 102.64% of the avg click for the month.
the Pct_above_Avg is 4175(day clicks) are above the average number of clicks(4067.4286)
Unknown column '/mnt/sdb7/mu[e]sic/Röyksopp/Coldplay - Clocks [Röyksopp Re' in 'where clause' — beside that umlauts are borken the string is cut before end, i just wanted to aks whether this is just a matter of the debug output or a "real error"
so would i do (count -avg) / avg * 100 ?
that is your column name?
maybe it mean the literal, but use the wrong quotes
no, actually it's a value of the column `path`
according to the error message, it was treated as a column name
yes, that was what confused me too but in phpmyadmin my syntax works
please read error message carefully then debug
i did
you use " ?
no
' ?
or ` ?
` definitely won't work
"`" for table / row "'" for value
" might or might not work
maybe you want to check your sql statement again
I don't think that makes sense.
http://rafb.net/p/7Zy21H31.html
no, that'll work.
please confirm the exact sql statement, all those variables and concatenation, you won;t know exactly what is the sql statement sent to the server
i just saw it, `
you expect to type ' but typed ` instead
okay, thanks
quotes
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
If I want to select fields from more then 2 tables in a query how will I make that work? Some JOIN ON or compare a field like I do when its 2 tables?
http://www.wellho.net/solutions/mysql-left-joins-to-link-three-or-more-tables.html
thanks
This is probably more of a discussion question, but I'm curios to know what are the ramifications of greating merge tables on the fly for lots (approx 500) of tables, with several million rows in each table.
is there any way to get mysql's REGEXP matching operator to be case sensitive?
use the BINARY keyword in your expression
COLLATE is probably the new replacement of BINARY
Thanks for the help guys! I'll try this.
like 'select binary col from table where col regexp yadda'
argh, 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 'WHERE `path` = '/mnt/sdb7/mu[e]sic/Air - 10,000 Hz Legend [2001]/Air - 10,000 Hz' at line 1 — what's wrong with this? *duff for all*
near WHERE, maybe just before WHERE
show the whole query
ah, got it!
select count(*) as value, value from table — i thought if i alias a column i could use its value else where ?
freibier!
thanks a lot!
look into nested queries.
hum
no error, but nothing updated
Installing the PEAR directory in roots directory could be the source of why an example from a book using pear HTML_Table wont work?
can i use UPDATE when content is NULL?
threnody, so without sub selects i can't use a alias else where ?
alias renames a column heading
Right. However, you can use an alias in the HAVING clause.
oic
you can order by, group by, etc
thxs
SELECT expr AS alias_name FR tbl HAVING alias_name 'k';
s/FR/FROM
I have an 11 gigabyte .sql dump file which I need to import into MySQL (it was originally exported from MySQL also). I use 'mysql dbname dump.sql', and it takes 8 hours to complete.
oic
_8 hours_. I have what I believe to be an optimized config for inserts, but this still takes _forever_ to complete.
Any way I can tweak this further to speed it up?
foreign keys in the database?
threnody, I don't believe so, but let me check
threnody, None
when you did the original dump, did oyu by chance use the –opt flag for mysqldump?
Nope
Schmee, This is actually a Wikipedia database dump (ships in XML, and I use their java tool, mwdumper, to convert it to .sql)
I though –opt was the default now, even if you don't call it
that's probably your problem. The dump file has a separate insert query for each record
Each insert is about 5M in size also
not that I know of, but I suppose it's possible
SQL
That's what the process requires at this point
Its weird, it seems to get progressively slower as more rows are added
can you show your table structure on pastebin?
Schmee, Sure, just a sec.
from the manual "…Note that all of the options that –opt stands for also are on by default because –opt is on by default…"
progressively slower suggests an index/key is being updated which will slow things down
cool. I didn't realise becasue I always used it anyway.
me too
need to find a way to turn off indexing until the inserts and updates are done
Schmee, http://rafb.net/p/QiropG48.html
Those are the three key tables in this particular import
you need to change the structure of hte tables to not have keys, then alter table ot add the keys after the import
Schmee, But won't that alter take 7+ hours on its own too?
nope
Ok, so truncate the tables, alter it to remove the primary key, import, then alter to add the primary key?
I remember reading in the manual that it's faster than to let the keys update during the insert
Query OK, 5365743 rows affected (0.22 sec)
So there's 5.3 million rows in each of those three tables
Let me alter that and see if I can get a faster import
I had a similar problem, but probably not to the same extent. I have a table with a couple of million entries (actually several tables) with an index that crippled the server during imports.
dont' forget to change the dump file to remove the create table sections
Wait, which index am I removing?
er… that'll be a problem I don't have an editor that will edit an 11G file
all indexes except the primary key
So in ep_en_page, that'll be page_random and page_len, yes?
and page_title
its not an index, its a unique key
And if unique keys also, why not page_namespace too?
look in the manual for '–disable-keys, -K'
yes, that one too, unless threnody has just hit on a way to bypass the keys altogether
hrm, checking/testing
alter table ep_en_page disable keys;
Query OK, 0 rows affected (0.01 sec)
hrmmm…
So now I have to check if the huge sql dump is actually doing a drop-if-exists on the table
no, it's a mysqldump argument. "For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted.
This option is effective for MyISAM tables only.'
Its doing about 1,000 rows/second, importing
grep
kimseong, grep on an 11G file is no small task
grep can be set to only retrieve the first x results, or is that egrep?
maybe more or less, just need to check the first table
probably better off with a perl one-liner
rest should ne the same
strings huge-ass.sql | less
Schmee, I actually used a perl one-liner to add table prefixes
oooo, wait
Should there be a COMMIT after every insert?
no'
threnody, Looks like the SQL dump doesn't include any DROP/CREATE statements, its just an enormous number of inserts
not unless you configured it that way, using command line arguments. mysqldump does create table by default.
mysqldump wasn't involved in this process at all
that explains a few things
The original source was XML, which was then converted to SQL INSERT statements by a java tool called 'mwdumper'
my apologies. I misunderstood
threnody, No worries, its a convoluted process, but the whole database is getting close to 1TB
So its enormous
I would alter table and remove all indexes except the primary key, import data, then alter table to add the indexes.
threnody, I'm trying that, but what's weird is… when I issue the alter to disable the keys (as pasted above), then do 'describe ep_en_page;', it still shows the keys.
Should it?
and switch to replication
not disable keys, delete them
disable keys is an argument for mysqldump command
: actually it's also used in create/alter table as well
Then why was it accepted above?
use the drop index syntax
back in a bit. lunch time
So both indices and the two unique keys?
just not the primary keys
Right
ALTER TABLE ep_en_page DROP INDEX page_random;
etc..
I think so.
then import data, then alter table and create the indexes
Yes, testing that now
it'll take a while, but not 8 hours
I'm going to be doing this over on a monthly basis, so the quicker I can make it, the better
http://projects.plkr.org/encycloplucker/
you should see a significant improvement. But replication may be your real solution.
replication?
How will that help me here?
brb
is mysql bad when it comes to joins? performance wise?
not if you index correctly, and write good sql
I was assuming you were doing some sort of db backup
threnody, Just rsync to a backup slice
Ok, keys are gone, testing an import now
replication can create a duplicate database on another server in real time
I wish there was some sort of feedback with the redirect import approach
Hello, sometimes I have an 'duplace entry error' using large INSERT INTO, its a good idea to use REPLACE INTO instead?
Only if you want to replace the conflicting record.
so, will fix my problem?
?
So to be able to use a column in the WHERE clause, it needs to be an index?
no. it simply speeds the query.
what is better for me, INSERT INTO or REPLACE INTO? is very ver bad to have that error?
Alright, thanks
Why did you say I didn't want a reverse behavior for the foreign key? What harm would that do?
look at INSERT ON DUPLICATE syntax. http://dev.mysql.com/doc/refman/5.0/en/insert.html
that erros is very very bad?
depends to what you want, insert, insert ignore, replace, insert .. on duplicate key, there are 4 different situations
no. it just prevents you from inserting dupicate data based upon your database structure. you can use '…INSERT ON DUPLICATE UPDATE..' to manage it.
go and study the difference of the 4 situations and decide what you need
but, REPLACE INTO does not use it for me autmagically?
depend to what behaviour do you want to have
Your parent needs to be there so that you can add children. You don't want to remoev the parent just because you delete a child, even the last one. If at some point you do decide to remove the parent row, make that a separate operation.
On the other hand, when you decide to remove a row in the parent table, the constraint will not allow the operation unless you also remove the children. ON DELETE CASCADE allows that to happen automatically.
Alright
Still don't see the reasoning behind the first case.
Why would I want to keep the parent record that's unused?
To be able to add a child.
It needs to be there first.
It's much like having a color table and a car table that refers to the color table. Just because you have no cars refering to one of the colors doesn't mean you no longer want to have that color available.
Hey
Hi all, I tried moving the location of mysql.sock out of /tmp because it keeps getting deleted, but something must have gone wrong
Im trying to find a record based on part of a field (the email address). Select * from User where …;
I specified a new location in my.cnf, restarted mysqld, but no go
I can't seem to figure out the where clause to find based on part of it
threnody, ping
WHERE x LIKE y
real 33m46.770s
Look that up.
Ah, like. Thanks
So I went from 8+ hours with keys, to 33 minutes without keys
Yeah, am looking it up now
holy-freaking-shnizit
Make sure your clients who use it also know where to look.
Describe the bad behavior.
when I login via ssh , I can get the mysqld_safe deamon to start, but I cannot connect to the socket: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/k3005/stijn/mysql/socket/mysql.sock' (2)
Who can't connect? Be specific. Is the socket file at that location in the error message?
ut oh
yes, the file is there. I try to connect with "mysql -u …."
Does the user rnning the client have access to that socket file?
I need to add a unique key on two fields in a table… but phpMyAdmin won't let me do it
What is the right syntax? I need a unique key called 'name_title' on 'page_namespace' and 'page_title';
Use the mysql command line client or query browser.
Xgc, I use the cli
I think so… "mysql -u root -p" gives the same result
Just use a comma separated list for the colunm names: primary ket (f1,f2) or unique index (f1,f2)
primary key, that is.
ALTER TABLE `ep_en_page` ADD UNIQUE INDEX ( `page_namespace`, 'page_title' );
That fails
!perror 2
No such file or directory
That's what that error means.
The client doesn't have access.
right.
or the file is not there.
the client used to have access when the socket was in /tmp though…. I just stopped the server, specified a new location in my.cnf, and restarted…. this seems to have created the socket at the new location (as far as i can tell), but I can't seem to connect anymore.
whew, ok… this seems to work: ALTER TABLE `ep_en_page` ADD UNIQUE name_title(page_namespace,page_title);
I can't believe this 8 hour process was just shortened to 33 minutes
The file is not there or the client doesn't have access. This is entirely a local file system issue. There's nothing mysql specific about this problem.
If the server is listening on the standard port, you should be able to connect via that.
Using a host and port as the connection details.
"mysql -S mysql.sock -u root -p". This seems to work fine.
That probably means the default is different.
yes, seems that way. have I forgotten to change a configuration file somewhere?
Alright, I guess I agree with that approach. Thanks a lot for the explanation !
The error messages tells you all you need to know. Check that the path in the error message matches exactly the path you used manually.
You're welcome.
looks fine, really. basically, I changed my.cnf, and set $MYSQL_HOME to point to my.cnf, and logged in. Is there anything else that needs changing?
weird… after giving the socket location explicitly with -S path/to/mysql.sock once, it seems subsequent attempts to connect with mysql now all work, even without the -S option.
ah well, it seems I can make it work then. thanks Xgc
what is information_schema for?
and cluster?
mysql.com/information_schema, mysql.com/cluster
I have a table with a `created` column (YYYY-DD-MM) whenever I insert a new row I wanted to delete the oldest row. How do I structure a DELETE FROM to delete the oldest row?
how to know what db or what table i'm using ?
select database() to see the current default db. ou are using whatever table you are specifying in a query.
select database;
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
arjenAU,
Would DELETE FROM `tablename` ORDER BY `created` WHERE * LIMIT 1 work?
where * is nonsense, but otherwise yes. and no need to backtick everything
Can I have a DELETE FROM without a WHERE? and whats backtick?
yes.
the `blah` you were using
see how what you typed there is not what I said?
kk
And another problem, is it possible to use GROUP BY and ORDER BY succesfully together?
Because im not having much luck
Yes, but probably not the way you're thinking. ORDER BY happens after GROUP BY
would anyone recommend Innodb as a db engine for a website?
it depends on what's the kind of u'r request
i've used it with success at decent load… but really i still consider myself a DB/SQL n00b especially when it comes to server config
Some people say it should be the default engine
or what do u'r mysql busy for
So it should be InnoDB unless another engine is specifically better
mixing 'em only makes sense if you are using aggregate functions or a having clause.
otherwise they essentially do the same.
question too vague.
Ah.
Lets say I have a table with title, created, and location.
I want to retrieve the last five rows, and then group them with their location
Is there an easy way?
arjenAU!!
I was looking for you the other day
and?
I managed to get our 8.95 hour SQL import down to 33 minutes
define last five?
oh good! used any of my hints for that?
Some, yes..
fab. I should start charging some time
That's an enormous savings with the Wikipedia imports, and now I can probably re-cook them weekly, instead of bi-monthly
hahahah
I'll give you a cut of whatever we make in sales
half an hr sounds good anyway. excellent to hear.
newest five, according to 'created'
righty. that'll be either a subquery or two queries. the first query would grab the last five using order by and limit, and then you order by that by location.
order by comes before limit, so you can't do it ina single query
Would I be better off doing it that way, or having a seperate table called "latest" that only had five rows total?
you could have a separate table with just the IDs of the latest 5 entries, and update that some time.
or duplicate the data… but then you might as well cache it on the application side rather than in a table
Thats the solution I was considering
A seperate table would also allow manual modification of the 'latest' listings
not doing that query all the time is best, regardless of how you phrase it.
don't make your life too complicated
k thanks.
no worries. I work for chocolate.
any preference on the type?
I'm a bit partial to white
do I need to do something special to select a DB once I've connected to MySQL
use dbname
or mysql dbname
I'm getting this error - Access denied for user 'database'@'localhost' to database 'database'
me?
swimrr, Did you grant user 'database' permissions to the database named 'database'? (and I hope those are fake, because having a user 'database' and a database named 'database' is really not a wise idea)
yes, those are fake
Every db you have, should have its own separate username and unique password
I thought I granted 'database' permissions
Ok, so just "grant add,drop to horsemonkey@localhost to database dwarf_porn;"
er…
"grant add,drop on dwarf_porn.* to horsemonkey@localhost identified by 'I-love-ponies';"
Is there a easy way to click though a mysql database and look at all the data?
hi.. im looking for MySQL-Max on the mysql download site.. for version 5.0.. does mysql still provide one?
Gokee2, "click through"? Its a database, not a desktop application
its not on the list.. unless they placed it on a different page..
you don't need it.
phpmyadmin or another gui
setuid, I am wanting a app to view the database
Gokee2, phpmyadmin
Ah ok… Can I install phpmyadmin on one computer to view a database that is on a different one?
can I do that through my cpanel?
I have no idea what a cpanel is, so I don't know
Gokee2, Yes
Ok thanks
Gokee2, If the database machine exposes port 3306
Which may not be likely, many do not
my control panel
how to set mysql only allow local connection? only bind to unix socket
skip-networking
i guess i'm lost because i've connected to mysql no problem, but can't access the database
I should be able to make it…. I just don`t want to install a web server on that comp
okei… i got that..
Hi.
I was wondering, is there such command i can use to check wether something exist in the database.
What i mean is:
select * from users where 'hi' isin `profile`
I made that up, but is there a way to check for something inside a column
look in the manual for LIKE and REGEX
ok, Thank you for the reply.
hello… can someone help me out with a simple myql cluster design question?
no problem
how simple?
ok great let me explain
the way i thought this cluster should work is the following.. and please forgive my mysql ignorance if such
have 2 server scooped out to be the data nodes that would store the databases..
have another 2 servers that i thought would run ndb_mgmd and be the sql nodes
then i have some web servers that would be requesting the data
i believed that the web servers would talk to the sql nodes and then the sql nodes would talk to the ndbd cluster nodes
is this correct ?
so basic flow of data would be the web server requesting data from sql node running on one server then the sql node gets the data from ndbd servers and sends back to the web servers
hmmm, sounds like you are not using a cluster per se, but really a load balancer and replication, with some form of caching. Correct?
i know there is a way to change the next auto_index value..but I cant find it
Using lib mysql client.. Is there any reason why if I use it too many times in a second that it would crash my program?
anyone know how i change that?
show .. i have tested the cluster node failover replicatin and that works.. tested the sql nodes or api nodes quering data from data nodes and that works but now when i go further in testing with the web servers quering the sql nodes, as i thought this is how it should work, i have run into
questioning if this is a proper design
i am using a load balancer in from of the sql nodes or sql api servers
ah I found it
that is part of my design but not in use yet.
do you need load balancer?
if you can put 1 mysqld in each of the web server or apps server, it should solve the problem since all the mysqld are active
i have a load balancer .. i think my design is correct (2 data nodes over 2 servers) and (2 sql api nodes on 2 servers that also do management daemon)
a single load balancer becomes the single point of failure
how many web/apps server?
i guess what i am having problems with is testing the web server to sql node query and getting results from the data cluster
you can run 1 mysqld in each web server
i have 2 Load balancers
which will load balance 4 web servers and 2 sql api nodes
maybe load balance your web servers
i thought it would be better performance to have sql node seperate from data node and web servers .. is that not ok?
i will suggest to remove the 2 sql node, install the sql nodes on the web server itself, 4 web servers with sql node on each
but then there is data on the web servers.. which i can not have due to security
of course if you want to load balance the 2 sql nodes, still possible
yea thats what i have designed..
the 4 sql servers are in a internal "bunker:" network for security .. which the LB fronts the 2 sql nodes
now i am trying to do my next phase of testing which is a query from a web server to a sql node server that should be able to see the databases of the sql data nodes but all i am getting is the inital databases that i assume are just on the sql nodes by default.. so how do i tell the sql nodes
to query the sql data nodes not themselves
mysql cluster uses ndb storage engine, then the data are stored in the data nodes
make sure that the tables are ndbcluster
yup i have that .. and i tested the replication btwn the two ndbd servers thats all fine
you should be able to read the same data from any sql node
i dont see the test db's that are on the ndb servers..
may have to create the database, the tables will appear automatically
hmm that may be it ..
i did have to do that on the other ndb
did not think i would have to do on the sql nodes also
on the sql node
ndb means what? ndb api?
or the data node?
data node.. process is ndbd
sorry missed the d at the end
you cannot do anything to the data node, right?
what do you mean do anything?
the data nodes listen to ndb api, which mysqld will send messages to ndbd, you cannot connect and work on the ndbd directly unless you use the ndb api
no.. i can connect to any data node (ndbd) server directly and create or query .. no problem..
using what?
mysql client?
ok i had to create the database on the sql node
now it works ..
yea using command line mysql
running on ubuntu
means you also have sql nodes running in the ndbd nodes?
mysqld on the same machine?
mysql cli can conenct to mysqld not ndbd
let me check .. i believe so .. i think i was having some issue so i did put mysqld to start on there .. newb move
yup .. sure do .. so i should only have ndbd running on sql data nodes
and mysqld on sql node
yes, unless you want to allow conenction to the same machine
perfect. so its normal that i would have to create the DB on the sql api nodes? i orginally connected to from the data node to the same data node to create the db .. thats where i probably started off wrong????
Good morning all. We recently upgraded from MySQL 5.0.22-log to MySQL 5.0.44-log on a CentOS server. We run a service in Tomcat using Hibernate on this, that used to work. However, since we did the upgrade, we get an error "ERROR JDBCExceptionReporter:72 - Data truncation: Data truncated for
column 'cadtjoin' at row 1" on the first date we try to write to the database. Has anybody experienced anything similar?
if the server's time is correct, why would NOW() register as 7 minutes ago?
ur time is 7min fast?
:-p
hehe
but i'm not comparing it to my computer's time.
good shot skipper
the server says it's xx:xx:xx when i run 'date' but when i created a record with NOW() it says that it is 7 minutes earlier.
4 PDT
the seconds are wrong because i had to do some clicking manually.
oh wait wait… i think i know why.
how to set mysql only allow local connection? only bind to unix socket
i think my host uses a different server for MySQL than it does for my user account. that's probably why.
skip-networking
arjenAU, where to use skip-networking?
_mary_kate_, ^
in my.cnf
_mary_kate_, cool, you from wikimedia
_mary_kate_, i do not have my.cnf, should I create it myself?
sure? it should be in /etc
run 'locate my.cnf'
I have a really big problem. A PHP include at the bottom of my page seems to be reverting all the MySQL UPDATE's I have done on the script in question. But the file PHP is including is a file which contains NO PHP… Any ideas anyone? I know it is the include because if I remove it or place a
die(); before it, it will work. Help.
More of a ##php question…
It is only affecting something to do with MySQL.
Well, the PHP is doing something…
Not exactly MySQL's fault
I bet your include has PHP
I bet it doesn't. Because I checked it at least 100 times. This problem has been with me for days.
Check ##php yet?
If i was looking for the word hi in a user's profile. is this correct: SELECT * from table WHERE profile REGEX `hi`
how about WHERE `profile` LIKE '%hi%'?
Iam using that right now,
Its not really working
how is it not really working?
well, it shows like 2 - 3 rows but it wont show liek the rest of the rows in the database
say 5 rows have the profile with word hi in it, and it only outputs 3 of them
do you how to use pastebin?
it helps to see code.
Yes i know how to use pastebin
and here is my sql code.
SELECT * FROM users WHERE `profile` LIKE '%".$search."%'"
yeah but maybe your code is the problem and not the SQL query.
do you get the same results from query the database directly? like from the command line or phpMyAdmin?
Oh, I havent tried that. I'll do that.
Hm works fine on the phpmyadmin.
For all you SQL gurus, how would you increment the count of some field in a row if that row already exists, and insert the row (with count 1) if it doesn't? Can that be done in one round trip to the database or do you need two queries?
Thank you my friend, i had a simple php problem. Phpmyadmin showed the problem. Thanks again
no problem.
I am looking for a software that have the mysql-query-browser, mysql-admin, mysql itself and this kind of tools in ONE, for windows, whats the name? mysql tools or what?
i'm not a guru but i'm pretty sure you can do that. the default value would be 1 and the query would take the current value and do a +1 to it.
you mean the software that the MySQL group makes?
!!
skype claims its your birthday!
INSERT … ON DUPLICATE KEY UPDATE
you mean the software that the MySQL group makes?
why not
lol
The mysql package itself comes with all that?
he left, anyone answer please
in replication.. you place the db name to be replicated in the my.cnf… what if you added a new db on the master. would that mean i need to update the conf and restart the master?
just dl and install. mysql.com has the download files. they all have installers. the server and the gui tools package.
Yes it is called MySQL Tools and consists of query browser,m administrator and other tools
Don't add any in my.cnf and it will replicate all databases
If you don't want replicating some just add it to replicate-wild-ignore list
how to view mysql command-line result page by page , like more/less in unix shell?
mysqlhelp
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
mysqlpager more
thank you
salle I will download mysql tools, does it mean i will have it ready to use or I still need to download a mysql server
xjkx, which mysql tool?
http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-gui-tools-noinstall-5.0-r12-win32.zip/from/pick
I will need to create an account, argggh
oh,no need
max_user_connections=0 is unlimit?
densin,have a try,and u will get it
it is value ,see when show varilable
thanks a lot
denis:yes, it means unlimit
but it is limited by 'max_connections'
thank you
what about if max_connections=0 ?
the "on duplicate key update" hint…. it works perfectly!
Ah. Great!
no one can login into mysql,expect root via mysqladmin
if set max_connections=0
We recently upgraded from MySQL 5.0.22-log to MySQL 5.0.44-log on a CentOS server. We run a service in Tomcat using Hibernate on this, that used to work. However, since we did the upgrade, we get an error "ERROR JDBCExceptionReporter:72 - Data truncation: Data truncated for column 'cadtjoin'
at row 1" on the first date we try to write to the database. Has anybody experienced anything similar?
very dangerous
y,be careful
hi i have an interesting problem with innodb engine, yet i have converted a database MyISAM to InnoDB and the next inserts autoincrement value is 2.
is that normal for InnoDB ?
from show table status?
just ignore that, it will be determined when you make the first insert after the server started
anyone ever had issues with mysql server running but not connecting? im on debian
from remote client?
nope
and its all using a socket file
what is the error
dunno it's a live server so as i saw it didn't work i restarted it
works after restart?
yeah
that's what's weird
and its not the first time (or only server) i had stupid issues like that
anyone has an idea?
brb
The_SNiFF whats the question?
can u scroll up? i restarted my irc client
The_SNiFF, i just joined
going to bed now
ok
thanks anyway
Really that hard to repeat?
no. he said he's going to bed
god am I enthused about databases
Yes
I imported my first monstrous data set
300M of lyric data
Nice!
170 thousand records
bashscripting on flat files took me hours to do anything with the data
mysql does it in 5 seconds
Using the right tool for the job
its like delicious cake or sex
i just want databases of everything now
ill find something to throw in my databases
make it queriable
maybe make it public
does anyone sell or offer free premade databases?
Sure
Mailing lists?
they sell mailing lists?
People do.
Shit. I could go harvest emails or home addresses
how much do they go for?
That's not good
We don't like spam
Then are there any good ideas ? :p
Lots of ideas
You can log our IRC conversations
into sql?
Yes
that sounds crazy
Why?
isnt there some table limit?
Yes
how big
!man how big
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/how big
I had taken a dump of all the databases present in my db, how do I restore them?
mysql database -p dump.sql
er
mysql -p dump.sql
Joelwork_, look at the dump.sql file, it should look pretty obvious
kk , I was wondering if I had to specify the database
thanks
itll recreate all the databases
delete if they exist
ok
http://dev.mysql.com/doc/refman/4.1/en/full-table.html
You may need to specify the database name if it is not specified in the .sql file.
ok
I think the dump was taken with –alldatabases
seekwill, the = comparison on text fields doesnt care about case?
Usually
but select distinct will care about case
No
alright
i've got like 5 lyrics from 'Guster'
and they are all coming up twice
because i put two data sources in
one GUSTER one Guster
how can I get rid of the duplicate guster?
or should i have made the artist name field an index?
Well, if there are two, which one do you want?
the first one
with the lowest id?
or just not to add two of any
You need to decide what you want…
here's a simple question. I have a query like "select url,size from logs where username = 'user' order by size limit 10" What I want to know is, is the resultant 10 records sorted from the whole table, or is the sort done on the first 10 records?
Whole table
LIMIT is done "at the end"
So your query will return the smallest 10 records
in the whole table
so that would work if I used limit 1000,10 as well? it would sort the entire table, then pick the last 10 records?
If you wanted the last 10, use ORDER BY size DESC
I just tried it but I don't think it worked that way
Well… now you know
ahh, the desc was the bit I was missing. Thanks for that
now I can save myself a lot of extra processing.
thanks again seekwill
np
hi
i database a bout 5Go is considered like medium large or huge ?
how do I select all indexes on a table in mysql 4?
index name and type
"select all indexes"?
What does that mean? Select for what?
how do I list the names of indexes defined on a table with sql?
!man show index
see http://dev.mysql.com/doc/refman/5.0/en/show-index.html
show index from
thanks
i only avec 2G of memory on this system
s/avec/hve
s/hve/have
I only have 512MB
s/have/hve
s/hve/avec
lol
Are you making fun of my 512MB?
I'm poor.
hello
the mysql tools doesnt install in win98?
doesnt work*
seekwill 1GB is $50
jacks-: Oh really? That's awesome! You can paypal $50 to seekwill@mysql.com. THANKS!
haha
How to migrate " `date` timestamp NULL default CURRENT_TIMESTAMP" to MySQL3? MySQL3 dos not support the CURRENT_TIMESTAME…
that is probably for the price of newer RAM, for older type can be more expensive or end of line even
just remove the default
but it will be updated on both insert and update
It seems there has no other way?
I dos not want to remove the default …
shpchp, yes,seems no way
the expected behaviour will insert the timestamp as default even in old version before 4.1
as long as it is the 1st timestamp col in the table
ok, thanks you guys
We recently upgraded from MySQL 5.0.22-log to MySQL 5.0.44-log on a CentOS server. We run a service in Tomcat using Hibernate on this, that used to work. However, since we did the upgrade, we get an error "ERROR JDBCExceptionReporter:72 - Data truncation: Data truncated for column 'cadtjoin'
at row 1" on the first date we try to write to the database. Has anybody experienced anything similar?
Just as feedback
the problem was that I needed to upgrade the jdk I was running.
i would like to do something like select foo, concat(field1, field2) as field3 from test wherefield3 = 'something';
mysql server upgrade has nothing to do with jdk, do you mean connector/j ?
this obviously doesn't wor. what is the correct way to do it?
mo, why does it not work?
alias cannot be used in WHERE
change it to its original form before the alias
select foo, concat(field1, field2) as field3 from test where concat(field1, field2) = 'something';
zongk, ah thanks. 
thanks. didn't know tat
mo, that was kimseong's answer
you can thank him
thanks kim 
mo but dont use a function in a where as an index wint be used
so is there a more efficient way?
much better to have a composite index and where field1='part1' and field2='part2'
and concat the answer
hello, trying to solve one thing, would somebody please help me. i have a highscore table for a game. fields are: id, name, email, score, news and dt(datetime). i have to pick 30 highest scores but.. i am trying to achieve that no names and emails repeat. 30 unique. my query looked like this.
SELECT DISTINCT * FROM highscore GROUP BY name, email ORDER BY score DESC LIMIT 30. but this gives me not what i want.
thank you very much in advance
anyone?
order by max(score)
hi
can somebody help mi?
me
i've just installed mysql-5.0.27 under Zenwalk linux
but there isn't preconfigured the db server
how can i do this?
just configure it?:
sorry i've solved the problem
the maker of the package didn't write the mysql main config commands for the install script
i need a little help. this query INSERT INTO `email` (alias,dest) VALUES ('test00@test.de','test–0001'); fails with ERROR 1136 (21S01): Column count doesn't match value count at row 1. it looks like the interpreter stopping at the — b/c it thinks a comment starts here (note the
quotationmarks) if i remove one of the - the query works. wtf?
lo lo
I've got another strange problem with my 2 node mysql cluster
select version();
5.0.32-Debian_7etch1-log
mysql on node 2 seems to take a lot of cpu when running a simple query (such as when I load a dynmamic webpage). loading the same page off node 1 is instant. stopping ndbd on node 1 makes loading a page off node 2 instant.
once I restart ndbd on node 1 as soon as the management server notices node 1 is back, node 2 is slow again 
running strace on the mysqld process is giving me nothing I can work with
Did you try that insert from MySQL command line client or Query Browser?
cli
Works fine in 5.0.37-community-log
Post the complete sql file to the pastebin.
ok, will take a while though i have to collect commands first
Is this not just one sql file? How did you run this?
If this was done interactively, maybe you typed something strange.
Post the create table statement as well.
http://rafb.net/p/Jm9mbE30.html
i am copy-pasting from a rather long file, trying it line by line so that i can make sure everythings ok
No problem. Post the create table for log.
hello!! I have a SELECT statement depends on SUB QUERY. In Sub query, I have two columns, 1. for iD, 2. for COUNT(*) used with HAVING. as there are two columns used in subquery, my whole SQL statement failed. how to hide the second column?
http://rafb.net/p/n5fMo149.html
and email
how to hide a column from showing in SELECT statement?
in result set.
?
The problem looks to be the trigger.
http://rafb.net/p/GaOkyV37.html
((SELECT (SELECT myvalue FROM tmp_table WHERE mykey='username') AS myval,NOW(),aid,bid,NEW.id FROM users WHERE user=NEW.dest)) is not correct.
i was fiddeling around with it until mysql didn't complain anymore
Remove the outer set of ()'s
that will make mysql complain
The problem was not your posted insert.
It's can't work with the ()'s.
I suspect you're removing the wrong set of ()'s.
excuse me, help me too
I could not find anything possibility in SELECT statement syntax
is it possible?
You should use this form: INSERT INTO tbl (column, list) SELECT blah, expr FROM tbl2 WHERE …;
that causes a syntax error
Not if done correctly as I showed.
what query can I run to get the only the latest entered record back?
don_martino, that was to you.
what I want is the 'ID' of the most recent record
i'll try again
Is it possible to hide a column from showing in SELECT statement result?
that would be my new trigger-insert-statement: INSERT INTO `log` (`user`,`date`,`acctmapid`,`acctusrid`,`addid`) VALUES SELECT (SELECT myvalue FROM tmp_table WHERE mykey='username') AS myval,NOW(),aid,bid,NEW.id FROM acctusr WHERE username=NEW.dest;
Hey all
does that look ok?
is it possible to do what i want?
I am unlucky, so many people are here, but no one respond. Anyhow thank you. Bye.
all my records have an ID number which is auto increment, i want sql to query and return the latest nukmber only
ORDER BY id DESC LIMIT 1
I'm having an odd issue with MySQL(5.0.45) on OS X 10.4.9. all was working fine for a while, now it refuses to start, any idea?
thanks
Kieran, check your mysql logs
You added a users table. Can you post that create table?
No. Don't use the VALUES keyword when using INSERT SELECT.
no, i might get into trubble releasing that much of the layout as i've done already. what would you look for in the user table?
ah, i just read over the VALUES thing.
sorry
I would only need the columns of the users table requried for this SQL. You already showed the column names. Surely that structure (that you already showed) wouldn't be confidential.
the VALUES keyword was the problem all along! i took it out and now it works. i was just too dumb to read your instruction carefully 
but it's working now. thanks!
You're welcome.
Where are the logs on OS X? they're not in /usr/local/mysql
Possible /var/logs
or /var/log/mysql
Nope, already looked there.
lsof to determine the real location if you can't find it.
moin
moin moin
Hiya … im not sure which place is best to ask this question….. but i have a problem … i have a software which posts logs to the database the server is running in uk and Australia the uk one works fine but when i check logs in database via phpmyadmin … the time stamp of logs is 1970
…. how do i fix this ???
Clocks probably run backwards in Australia.
ugh - no ideas about my slow sql cluster node problem ?
Dimensions, learn about the timestamp type or fix the code, locate the source of error
so you mean its coming from the code has nothing to do with phpmyadmin
or the mysql version or the setup of the timestamp field
found the log [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
Hmmm - does anyone have any links to PHP5 vs. 4 performance benchmarks?
Any ideas?
Kieran, odd a number of people reporting that as a problem recently
Any fixes that you know of? I'm totally new to mysql, well, running a mysql server anyway
no, I dont have osx so dont help with install issues on that, just notice a few questions
Alright
google to the rescue
That would normally happen if you never initialized the mysql database.
Everything was working fine, I had added databases etc and I was then getting unable to connect via socket messages and then I realised it wouldn't start.
Changing your config file or mysqld command line options could also cause this behavior.
Sometimes this happens following the first reboot/restart using a config file you never tested.
Meh, this is where OS X annoys me
everything is in non standard places and finding your way around is almost impossible.
I think I'm just going to install debian on a seperate computer and set mysql up there
ok - even more bizarre
stracing ndbd on node 1 makes node 2 respond quicker
hiya!
I have a very large mysql database…. like 20 gigs
how do i go about backing something like that up and moving it
what?
dmb062082, if you have got to that size and dont know how to back it up……
sure I could abck it up
ah, backup, ok
into a 9 gig gzip or something
even 9 gigs
how the heck do I move something like that
anybody experience with IS NULL optimization? (with indexes)
better yet
how would i break it into smaller queries
bobster, state the real problem
when i do an EXPLAIN on my SELECT.. it shows in type = ref.. instead where i expect the ref_or_null
WHERE deleted_at IS NULL
does the query plan (with WHERE deleted_at IS NULL) use this index and optimization for IS NULL?
bobster, but what does it say in the possible keys col
is it appropriate here to post up a select query to get an opinion, or do we paste?
pastebin
try http://pastebin.ca or http://pastebin.mysql-es.org
thanks archivist
perhaps the explain as well and create
it mentoins the index_name
well big dump will come in handy I think
adios
but type of the plan is: ALL (fulltable scan?)
then to an analyse table
do
if the nulls are 50% of the table then thats the fastest way
or try with force index to see if its quicker
could do with some help deciphering this http://pastebin.ca/633006
analyze table doesn't mention anyting bout nulls.. uuhm, how to?
force index in the SELECT-statement?
opalepatrick, like '% silk %' cannot use an index, rewrite to use fulltext
!man analyse table
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/analyse table
ah ok, thanks a lot archivist will go and do that
!man analyze table
see http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
i'll try
thanx the_wench and archivist
bot
I'm *not* a bot! I'm a real buxom wench
archivist, should have checked those fields are all FULLTEXT type
lol
nothin mentoined about an option "force index" in then man analyze table
its a type of index
there wouldnt be
talking to me
?
its a select option
yes
ok, let me get this right, lol. I have already created a fulltext index for those fields.
thanx ive found it in the F.M.
explain SELECT * FROM target_groups USE INDEX (ui_target_groups_code) WHERE target_groups.deleted_at IS NULL;
SELECT * FROM target_groups USE INDEX (deleted_at) WHERE target_groups.deleted_at IS NULL;
ALL, possible_keys: deleted_at…. and index in table really is on deleted_at (which is NULL-able)… is this allright?
if it works and is fast
thanks for your help, I have just discovered how to do a match against boolean for more than one term, so that will do a much better neater job all round! Thanks for your prompting.
i don't know this yet.. it's in development.. but i wanna know whether my DDL (index) for IS NULL optimizations works?
funny I had done this on a buch of other queries but didnt know how to + or - multiple terms. Seems so easy now!
, I was originally typing just OC4J. And Oracle AppServer
