Re: Questions about indexing
With such a small database it really boils down to just being tidy; you don't want indexes you're not going to use. Well the database is going to be like 200MB and executing several hundred queries a minute. Thus my concern about speed. Is a P4 w/ 1GB RAM going to choke and die, or will indexing help out? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions about indexing
I have a database I'm using for a MMORPG (well, it isn't very MM because I'm something of a noob), and I have a few questions about indexing. I am storing world data in a database. In order to keep everything as swift as possible, I have indexed everything. And I really mean, everything -- a few dozen columns on a half dozen tables. My question is, is this the right way? I figure that since the world data won't be changed very often (once I design the map that's going to be it) the increased time for INSERTS won't matter, because SELECTs will be very speedy. But I wanted to double check with this group because the mySQL manual says that a large number of disk seeks will be *slower* with indexing than without. I assume this means INSERTs because INSERTs have to be in order, and that since the world data is only a few megs it won't be seeking at all but in RAM. Am I right? I'd appreciate any comments. Thanks in advance, -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
perl DBI vs. prepare and execute do
I am using the Perl DBI to connect to a mySQL database. I am using prepare and execute statements to send the query to the database and then execute it. Is there any benefit to doing this versus using do? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting the value of a row you just inserted
Is it possible to SELECT the id (indexed PRIMARY KEY AUTO_INCREMENT) of a row that was just inserted? I'm using PHP, I don't know if that makes a difference? Something like: INSERT INTO foo (bar) VALUES (bar); SELECT last_insert(id) FROM foo; Also, are there any problems with using this in an environment where you're forking processes? Thanks in advance, -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enabling Transactions
I am having a problem in one of my scripts and I need to use transactions to fix it. When I looked it up on the mySQL documentation I found: If you are using transaction-safe tables (like InnoDB or BDB), you can put MySQL into non-autocommit mode with the following command: Does this mean that transactions will not work if I type in a CREATE TABLE command? Are there any problems with switching to InnoDB or BDB? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hostname
is there a way to find out which host I am connected from? Does the solution have to be a function called within mySQL or would you accept a function called by PHP or Perl? -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation of MySQL
Well you could: (assuming an installation in /usr/local/mysql) # /usr/local/mysql/bin/mysqld --user=mysql /usr/local/mysql/daemonlog /usr/local/mysql/daemonerrorlog But you should probably consult the manual under the installation for an explanation of exactly what the above does. -Dan On Sun, 2003-09-21 at 10:43, Eduardo Melo wrote: Hi All ! I have allready installed the MySql on my server. How do I start the Mysql ? best regards, Eduardo F. Melo PRO-IT Informatic _ MSN Messenger: converse com os seus amigos online. http://messenger.msn.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new MySQL Server
I believe I saw something in the mySQL manual about going with a 64 bit system if it was an option because mySQL uses lots of 64 bit data types. So you will see a significant increase in speed with 64 bits versus 32. :: drools at though of Apple w/ 8GB RAM :: :-D -Dan On Sat, 2003-09-20 at 08:11, Jon Miller wrote: I would like to get a viewpoint from the members on the following: 1) the amount of $$$ is NOT a consideration Looking at building a MySQL server to handle in-house critical work. Web = page data is generated from the database plus constant inputting of data = and calculation. Application to be run: Red Hat 9.0 PHP4 MySQL 4 Apache 2 SSH I'm looking at the following: 1) IDE RAID5 with 4 x 120GB drives 7200RPM ATA100 2) Sony CDT or AIT tapebackup 3) 2 x 10/100/1000 Intel NIC 4) Intel P4 3GHz CPU 5) 2GB PC3200 ECC Memory Motherboard consists of the following: Socket 478 Intel=AE Pentium=AE 4/Celeron=AE NetBurst 4 x DDR SDRAM PC3200 ECC Registered, 4GB max. 5 PCI, 1 AGP 8x (1.5v only) Highpoint HPT374 UDMA/ATA 133 RAID Broadcom BCM5705 Gigabit Ethernet ATX Now I've worked with the Broadcom NIC and that works great, but I have not = worked with a Highpoint RAID card. Does anyone have good and /or bad = experiences with this card or should I buy a separate card? My other option is to go with a SCSI system using a Adaptec RAID card, = etc. Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to find out if a table exists?
I am trying to make my PHP script autodetect when a table in a mySQL database exists, and when it doesn't, create it. Is there some way to do something like: SELECT * FROM tables WHERE name = table_name; And get a result I could test for truth, and thus run my script? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How Fast is COUNT()
I am creating a script which will grab a random row from a mySQL table. Right now the way it is set up each time it calls COUNT(). I was thinking of creating a script and running it as a cron job to update count once every 10 minutes and store the number as an indexed array in the table. It seems like a lot of trouble, so how much overhead does COUNT have? Thanks in Advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 has been released
Wasn't there just an announcement that 4.0.something was released? -Dan On Mon, 2003-09-15 at 12:52, Lenz Grimmer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 3.23.58, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. It includes a fix for a potential local security vulnerability which has already been applied to MySQL 4.0.15 as well. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: * Fixed buffer overflow in password handling which could potentially be exploited by MySQL users with `ALTER' privilege on the `mysql.user' table to execute random code or to gain shell access with the UID of the mysqld process (thanks to Jedi/Sector One for spotting and reporting this bug). * `mysqldump' now correctly quotes all identifiers when communicating with the server. This assures that during the dump process, `mysqldump' will never send queries to the server that result in a syntax error. This problem is *not* related to the `mysqldump' program's output, which was not changed. (Bug #1148) * Fixed table/column grant handling - proper sort order (from most specific to less specific, *note Request access::) was not honored. (Bug #928) * Fixed overflow bug in `MyISAM' and `ISAM' when a row is updated in a table with a large number of columns and at least one `BLOB/TEXT' column. * Fixed MySQL so that field length (in C API) for the second column in `SHOW CREATE TABLE' is always larger than the data length. The only known application that was affected by the old behaviour was Borland dbExpress, which truncated the output from the command. (Bug #1064) * Fixed `ISAM' bug in `MAX()' optimisation. * Fixed `Unknown error' when doing `ORDER BY' on reference table which was used with `NULL' value on `NOT NULL' column. (Bug #479) Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/Ze5NSVDhKrJykfIRAjdyAJ9eg1CTafcFv+U8W9GwcCPpU7m1XQCeMSCp xo6EMYY1Ixk81fveHOC+OQc= =UuFh -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to pipe wildcard searches into mySQL?
I'm writing a search engine to query a database to my site. I know how to use a WHERE product_name = foo if somebody enters the exact product name, but how could I do something like: WHERE product_name = *foo* so all results containing foo in product name would be returned and not just products named only foo? Thanks in Advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I display images from a mySQL Database in a web page?
I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? Yes, my client's server is running with particularly restrictive PHP safe mode settings and has informed me that dynamic images are a /must have/. :: bangs head against wall. starts to bleed :: So, it is relatively easy to store images into a BLOB, and slightly tricky to chop them up into the packet size and reassemble them. But trying to figure out how to do this has boggled my mind and I appreciate all the help of the community. :) -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storage efficiency of VARCHARs
I am curious about the internal representation of VARCHARS. I cannot find any documentation on it in the mySQL manual. Will a VARCHAR(10) take up less space then a VARCHAR(80), even though they are variable? Thanks in advance, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does mySQL support a boolean data type?
I ran a search through the mySQL manual and google and could not find a satisfactory answer. Does mySQL support the declaration of a boolean data type? Currently I am using VARCHAR(6)s with either 'TRUE' or 'FALSE' and would like to cut down on the storage. Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does mySQL support a boolean data type?
why don't you use int(1) and set it to 0 or 1? Storage space is an issue because I am designing a very large database for a client. Every byte I save per row will translate into many many megs. So if all I need is a single bit for true or false I want to get as close to that single bit as possible. (Although most bools end up being more then a single bit because of architecture issues). So, to put a long story short, I am trying to make every column's internal data structure as tiny as possible. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: derived tables
Are you trying to do this within a programming language? I use similar queries using PHP and have no problem. Although I would write my query like: SELECT tbl_name FROM user_table WHERE user = 'guy'; And then use the result to run a select, update, insert, or whatever -Dan On Sun, 2003-07-13 at 13:01, Shawn McGinn wrote: I would like to select data from a table where the table name is located in another table, and I am using the following query: select t1.* from (select tbl_name from user_table where user=guy) t1; This should return the data from table 'tbl_name', but I only get results from the subquery (ie. the query as a whole returns 'tbl_name', not it's contents) Where am I going wrong? I am using version 4.1.0-alpha-standard. Shawn -- Shawn McGinn [EMAIL PROTECTED] UNB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is it possible to dump images into a database?
Can anyone point me to a reference on how to insert images into a column in a mySQL database -- or is that not possible? Thanks in advance, Dan Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cloning a Database
couldn't you dump the database and then use the backup to reload the database? -dan On Tue, 2003-07-08 at 13:58, Boris Villazon wrote: Hi I need to know if is there anyway to clone a existing database (structure and data)? If yes, how can I do this? I looking for a SQL command. Thanks in advance and best regards Boris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Typical newbie frustrations
You're using Windows, so I can't make any promises because this is what
I'd do under *nix, but when I first set up mySQL if I type:
# mysql -h localhost -p
The password will be denied, because no password is needed. So I'd
type:
# mysql -h localhost
And get in. This is under the /root/, and I can then:
mysql USE mysql
mysql UPDATE user SET password = password('pass') WHERE
username='root';
And, of course, on any created database I can
mysql GRANT ALL PRIVILEGES ON some_db.* TO username;
mysql FLUSH PRIVILEGES;
Disclaimer: I'm not looking at my reference card and typing rather
quickly so my syntax may be off a little here and there. Hope that
helps!
-Dan
On Tue, 2003-07-08 at 15:15, Kraig Olmstead wrote:
If there's a FAQ I'm missing, please point me to it.
v 4.0.13-NT
I'm a C++ programmer trying to learn Java and JDBC (Java Database
Connectivity). Obviously I need a database to bounce off in order to do
that. Enter MySQL.
I am going in through the command line shell. I am unable to do much
because I don't seem to have privileges (e.g. create a database). The
WinMySQLadmin 1.4 tool has my local user and host correct - i.e. they
match what's in the my ini Setup tab of the same tool. I'm able to
get in using that host and username, but whenever I try to use my
password I am denied.
I'm guessing that since I don't provide a password I'm some sort of a
guest. The password I type in in the same as the one listed in the my
ini Setup tab and the users match both that listed in the Environment
tab and my ini Setup tab.
My apologies in advance if I'm doing something stupid beyond words here
or if this is something listed in a FAQ.
I may simply uninstall/reinstall to verify that I have everything set up
correctly.
KO
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
