| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Comments:
<dampja1> I have a table with 4 columns, a,b,c,d... a,b,c is the primary key... one script I'm writing tries inserting a row a,b,c,0 where a,b,c match another row (hence it fails to get inserted), is there a way in the same sql query to say that if a,b,c,1 exists, switch the 1 to a 0?
<BeK> hi
<dampja1> like an update that does an insert if the row does not exist
<will[werk]> !m dampja1 REPALCE
<SQL> dampja1: Nothing found
<will[werk]> !m dampja1 REPLACE
<SQL> dampja1: (REPLACE Syntax) : http://dev.mysql.com/doc/mysql/en/REPLACE.html
<Julian|Work> apalac
<BeK> i get a strange error trying to connect to mysql
<BeK> Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
<dampja1> thank
<dampja1> s
<BeK> what can i do ?
<Pyretic> Duesentrieb: yeah it's def. trying through a socket, but how can i enable both external and internal/socket/localhost connections ?
<will[werk]> dampja1, That's a DELETE WHERE a,b,c, INSERT a,b,c. See if that matches what you want.
<will[werk]> !man Can't connect to local
<SQL> (Can't connect to [local] MySQL server) : http://dev.mysql.com/doc/mysql/en/Can_not_connect_to_server.html
<will[werk]> BeK, Try that
<BeK> okey
<Marre> Hi everybody. how would you do to speed up a search with an wildcard in the beginning. I have a big database. around 2 gig I have put an index on it so a query like 'my_seek_data%' goes fast. but a query like '%my_seek_data' goes a lot slower. can I index it somehow so it goes faster.
<will[werk]> Marre, Not really. You may check to see if a FULLTEXT INDEX is what you need
<will[werk]> !m Marre fulltext search
<SQL> Marre: (Full-Text Search Functions) : http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html
<Marre> fulltext can't have a wildcard in the beginning
<will[werk]> You might not need a wildcard in the beginning... I don't know your exact specifications.
<will[werk]> With a wildcard in the beginning, there is no way to speed that up.
<Marre> will[werk]: are you sure
<Marre> ?
<BeK> will-werk: i read it and my mysql file exist
<BeK> will-werk: i read it and my mysql.sock exist
<BeK> nix 2 [ ACC ] STREAM LISTENING 75064172 /var/lib/mysql/mysql.sock
<BeK> but still go the same erro
<will[werk]> Marre, I am sure...
<will[werk]> BeK, Sorry, I don't know...
<BeK> ok
<archivist> !perror 111
<SQL> System error: 111 = Connection refused
<Marre> will[werk]: What do you think about using a regex. would that be faster?
<will[werk]> Marre, No
<will[werk]> It still has to read each row.
<will[werk]> Marre, What are you trying to match?
<Marre> I have a big database of companies and company info.
<Marre> I would like to search for example *ercedes*
<Marre> and i should find mercedes. And i does. it just take some time.
<Duesentrieb> Marre: well, the db can not do magic. It would have to index every possible substring of every string in the database. That's a lot of index entries...
<will[werk]> Marre, No way to do that fast.
<will[werk]> Marre, I'm doing a LIKE %string% now, but it's in a MEMORY table.
<Marre> Duesentrieb, yes I know. but I was hopping I could somehow maybe index from letter 3-5 or something
<will[werk]> heh
<Duesentrieb> i don't think so.
<archivist> write your own index
<Duesentrieb> and you'd need 2-7 too, and 4-5, and...
<will[werk]> That wouldn't make any sense. You're better off creating another column, with that substring, and indexing that.
<will[werk]> That's ***uming you're always missing the first letter, or first two, etc.
<Marre> will[werk], I guess youre right
<will[werk]> :)
<Marre> Maybe If i reverse everything I want to search for a put it in another column. the I can check the query and just reverse it. the it would be faster
<will[werk]> Marre, Extract/denormalize a column or two, put it in a MEMORY table, and do LIKE %x%
<will[werk]> Only if you're checking the ending.
<will[werk]> In your Mercedes case, it wouldn't work for Mercedes-Benz
<Marre> but I need a lot more ram If i use a memory table
<will[werk]> Yeah
<archivist> just dont let typos in
<will[werk]> Marre, Well, RAM is cheap. But also, only put into memory the columns you need.
<Marre> I guess the users just have to wait a couple of seconds.
<will[werk]> heh
<will[werk]> If it's over the Web, it's perfectly normal.
<will[werk]> Network transfer speed is your limiting factor anyways.
<Duesentrieb> make an option: [x] substring search (slow)
<Marre> I have a lot of ram. I can fit any more. but the server does a lot of sql qry.
<will[werk]> How many columns do you want to search through? How many rows?
<Marre> Have anyone tried using solid state disks?
<Marre> colums depends. but around 20. rows around 7million
<will[werk]> That you want to search through? geez...
<Marre> yes i know
<Marre> it's big
<will[werk]> 64-bit is nice...
<Marre> willl i go faster if i split the database somehow.
<winmutt> can a mysql4.x client connect to a mysql5 server?
<Marre> will it
<will[werk]> Sure
<will[werk]> Marre, Depends
<winmutt> will a mysql4.x client connect to a mysql5 server?
<will[werk]> Marre, Sometimes.
<will[werk]> winmutt, Sure
<winmutt> will_: no problems?
<Marre> seems like I have some thinking to do.
<will[werk]> There could be problems.
<winmutt> have to use old pwd encryption?
<will[werk]> winmutt, What's the problem?
<winmutt> i dont yet
<will[werk]> !m winmutt old client
<SQL> winmutt: (Client does not support authentication protocol) : http://dev.mysql.com/doc/mysql/en/Old_client.html
<will[werk]> If you're using a 4.0 client.
<winmutt> but its time to upgrade to get views
<winmutt> and other goodies
<will[werk]> You should upgrade the clients regardless.
<winmutt> ya mostly 4.1 but some 4.0
<winmutt> cant
<will[werk]> 4.0's will have problems. Follow guide.
<winmutt> cant upgrade 2000 servers just because i want views
<will[werk]> Why not?
<I3ooI3oo__werk> yes you can
<winmutt> ive read the old cient auth issue, and corrected for our 4.1 server
<Marre> will[werk]: thanks for the chat. take care.
<winmutt> will_: because clients do what they want when they want and god knows what the have compiled against the old libraries
<winmutt> clients being ppl that pay me money not mysql client
<will[werk]> winmutt, You do't have to correct for 4.1 clients. They are using the new schema.
<will[werk]> winmutt, heh ok
<Andy298> how can i enable root to connect from "192.168.0.11"
<Joe7|crap|> hi
<Andy298> do i create a new account or enable it on the other?
<Joe7|crap|> how long should it take to insert 14.000 rows into a table? (fields of the rows: 4x int)
<PinoSanto> hi! How can I send an .sql file query to the sql server with ASP.NET? I know how to send a string command, but i have many complex .sql files necessary to initialize the sql database.
<Andy298> do i create a new account or enable it on the other?
<HeliosTech> hi
<PinoSanto> hi
<HeliosTech> i want to make al match against in one query
<HeliosTech> by priority
<HeliosTech> cl***ic one, then add IN QUERY EXPANSION
<HeliosTech> i want to paste the Q2 at the bottom of the Q1 (Query) but with only unique result ?
<HeliosTech> also i have to make this function, 1) IF data exist in DB then UPDATE hits ELSE INSERT data , can it be done in only one query ?
<will[werk]> !m HeliosTech insert
<Andy298> how do i test MYSQL is working from PHP?
<SQL> HeliosTech: (INSERT Syntax) : http://dev.mysql.com/doc/mysql/en/INSERT.html
<will[werk]> HeliosTech, Look for "ON DUPLICATE KEY UPDATE
<will[werk]> Andy298, Check phpinfo(); to see if you have a mysql section.
<HeliosTech> will[werk] great
<mephistopheles> !delete
<mephistopheles> !m mephistopheles delete
<SQL> mephistopheles: (DELETE Syntax) : http://dev.mysql.com/doc/mysql/en/DELETE.html
<Andy298> ok - i don't! how can i enable it :)
<Andy298> ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql
<rene-> hello, i want mysql to bind to both 127.0.0.1 and its ***igned IP address? how do i get that?
<Duesentrieb> rene-: set the bind address to 0.0.0.0
<Duesentrieb> that will bind it to all interfaces
<will[werk]> Andy298, Ask in #php how to enable mysql support in PHP
<rene-> cool
<Duesentrieb> and consequently to all ip addresses
<will[werk]> rene-, no, comment it out.
<will[werk]> Why even have that option?
<Duesentrieb> will[werk]: why?
<will[werk]> That's my question :)
<will[werk]> Comment it out altogether.
<rene-> both solutions work for me, but is it possible to manually specify two ip addresses?
<will[werk]> No.
<rene-> cool
<will[werk]> You can only bind to one IP. But I guess 0.0.0.0 is special.
<Duesentrieb> yes, it means "all interfaces"
<Andy298> ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql
<winmutt> my coworker thinks that triggers and views should not be part of a database
Return to
#mysql
or
Go to some related
logs:
ubuntu phion
+share usb disk +samba
Gentoo Gtk WARNING **: cannot open display:
fluxbox 1.0 font big
python * argument dictionary tuples
Automatix Ubunut
lsagp
#debian
#css
python unscriptable object error
|
|
