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!