to improve speed of accessing the mySQL database
for my web application? I'm running MySQL 3.23.56 on a linux box.
Thanks anyone,
BD
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
. Should I
also enter tick marks? I tried that too.
BD
-Original Message-
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Friday, January 07, 2005 11:20 AM
To: BD
Cc: mysql@lists.mysql.com
Subject: RE: Slow queries, need advice on how to improve; key_buffer?
[snip]
I tried to improve
other
suggestions as to what
I could do to reduce query times down to a reasonable 1-3 seconds?
I just bought the book
http://www.amazon.com/exec/obidos/tg/detail/-/0596003064/102-0076963-3409775
?%5Fencoding=UTF8v=glance
but it has not arrived yet. Any suggestions in the mean time?
BD
PROTECTED]
Sent: Friday, January 07, 2005 12:23 PM
To: BD
Cc: mysql@lists.mysql.com
Subject: RE: Slow queries, need advice on how to improve; key_buffer?
[snip]
The problem now is, this did not do anything to improve the query and parse
times.
I'm testing out an on line store which has about
To: BD
Cc: mysql@lists.mysql.com
Subject: RE: Slow queries, need advice on how to improve; key_buffer -
zen-cart?
[snip]
The application I am using for the site is www.zen-cart.com so I'm not sure
I can do anything about changing the table indexes because it is a pre
written php-MySQL open source
Hi,
I'm attempting to improve MySQL speed/performance by adding key_buffer=50M
to my my.cnf file for [mysqld].
Upon stopping and then restarting MySQL,
I'm getting a 0 DB_ERROR_NOT_CONNECTED error when visiting
my php-MySQL web application via http. When I delete the server variable
in my.cnf
At 04:58 AM 4/23/2002, you wrote:
Hi!
How do check if a certain table exists? I have to know if
the table exists and then make a query in that
table. I mean to use it in a function so the SHOW TABLES
is not an option...
I have tried the sysobjects solution but it comes back with:
table
At 02:10 PM 4/23/2002, you wrote:
query, table
When I run the query:
SELECT LAST_INSERT_ID() as id FROM myTable;
It returns the last inserted Id, but why does it return that value for every
row in the table.
for example.
if there are 5 total records in the table. and the last inserted Id is
At 01:48 AM 4/16/2002, you wrote:
Greetings,
I would like to ask you following question:
Is there graphical user interface for managering MySQL database? I think
command line-based interface is quite clumsy...
Regards,
Vesa Kela, Helsinki Finland
Kela,
Try MySQLFront from
At 10:20 AM 4/16/2002, you wrote:
Hi all,
I'm looking for a tool like Enterprise Manager (I am talking about a
Windows client)
I downloaded MyCC and it looked a lot like it and hoped that it would work.
Don't get me wrong it does work but with a couple of glitches.
I don't know If those can be
Is there a fast way to update a column in one table with the column value
in another table? Here's the catch. I need to do this for up to 10,000 rows.
I would like to do this with one sql statement rather than writing a PHP
loop to do it.
I have 2 tables, Dept and Employee. I want to count the
At 10:09 AM 4/12/2002, you wrote:
Hello Everyone,
I've only just started trying to use MySQL so please forgive my
ignorance if this sounds like a really basic question!
I am trying to search a text field within a table that contains
several keywords; for example the column
At 09:13 AM 4/11/2002, you wrote:
Ok so this really doesnt have anything to do with MySQL but I thought this
might be a good placeto ask.
Ok heres the problem. I am constructing a Message Board with MySQL and php
(was usingASP) and I dont want the user to reload/repost data. For
example, say the
Son,
At 09:48 PM 4/7/2002, you wrote:
mysql desc threads;
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| thread_ID | int(11)
At 01:54 PM 4/5/2002, you wrote:
I have seen many people saying that InnoDB is a great deal, that InnoDB
rocks, etc. and I am concerced about how much better InnoDB is compared to
MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB
have foreign keys support, but I deal
At 10:16 AM 4/5/2002, you wrote:
Actually, according to the objective eWeek test results at the link
provided in another reply, the gap between Oracle 9i and MySQL 4.x is
rather slim...
Gregory,
A point that was sadly missing from that article was what was the
cost to create the Oracle
Lenny,
You might find it easier to use a GUI like MySQL Front from
http://www.anse.de/mysqlfront/. It will save you a great deal of time. Just
enter (or copy and paste) the SQL into their query window and press a
button. Editing your errors are also a lot easier. It is free or you can
Here are a few easy philosophical questions for a change:
1) For performance or storage benefits, is there a difference between a
Unique Index and Primary Key?
2) Is there a reason why I should use Primary Key instead of a Unique Index?
3) Why isn't there a syntax for create primary index
Paul,
Do you know if there is any performance differences between a
unique index and primary key (if there are no nulls)?
Is the index file format the same as the primary key?
Brent
At 05:09 PM 4/5/2002, you wrote:
Here are a few easy philosophical questions for a change:
1)
At 09:19 AM 4/4/2002, you wrote:
Can someone please recommend CASE tools that function well with MySQL
databases?
Need to do some reverse engineering ... create the ERD, data dictionary,
etc
Regards,
Michelle Smith
Michelle,
CaseStudio 2 is quite good at around $500 which will
At 12:16 PM 4/4/2002, you wrote:
Is it possible to access a sql statement to mysql database through
Internet explorer ? such as :
http://www.abc.com?select * from test
Thanks.
--
Best regards,
Ridwan
Goldbase Technology
Ridwan,
Of course, with something like PHP to get
At 04:23 PM 4/3/2002, you wrote:
hello,
i have plans to create a complex SQL database.
before that i'd like to draw a scheme of it.
could you please advice which
program(freeware/shareware)
would fit for that purpose?
any links to pages, tutorials about graphical
databases projecting would be
At 10:28 AM 4/4/2002, you wrote:
On Thursday 04 April 2002 09:48 am, you wrote:
I think you'll find that the RISC systems have fewer processors and run at
lower clockspeeds for the same total performance. 100k is a HUGE amount of
money to drop on a system. You could get 2 full racks of high
At 10:43 AM 4/4/2002, you wrote:
Hi david,
Just create another table in your database with the structure and data of
the table. See CREATE TABLE ... SELECT ... syntax at :
http://www.mysql.com/doc/C/R/CREATE_TABLE.html for this task.
Then update all the category values from 2 to 4 in that
At 06:20 AM 3/28/2002, you wrote:
On 27-Mar-2002 Ken Anderson wrote:
What is the fastest way to check if a table exists?
Do a select and catch the error?
Try to create the table, and catch the error?
Other options?
Thanks,
Ken
Yes, in php:
function tableexists($tbl)
At 04:30 AM 3/27/2002, you wrote:
Hi
I'm running mysql 3.23.36 on a Red Hat 7.1 with the standard kernel.
I have a /var partition (ext2) with 6.5 gb total space.
One of the tables are almost running out of space, it is almost 4 gb. is it
the filsystem that prevents files larger than 4 gb, or
At 02:32 PM 3/26/2002, you wrote:
Hi, I'm new to this list, hopefully, I can contribute some wisdom :)
At the moment, I have to update a large database to be multi-company-ready,
so I basically have to add a Column Company_ID to nearly each table.
What would be the easiest way to do this?
I
At 06:21 AM 3/21/2002, you wrote:
i am looking for a ER Design Freeware tool, i'd like to konw if does
someone here knows about something like that, the tool name and,
if possible, the url for download.
currently i am using a DDT (Database Design Tool) V1.5, however
i think it's too simple with
At 05:53 AM 3/22/2002, you wrote:
Hi, I'm using a db with few tables, one of which is reported below:
CREATE TABLE `keywords` (
`keyword` varchar(128) NOT NULL default '',
`codice` varchar(16) NOT NULL default '',
`timestamp` int(11) NOT NULL default '0',
`soundekw` varchar(32)
This *might* make your updates a bit faster. I don't think MySQL optimizes
updates this way (it would be nice if it did.)
Depending on your data it may be possible to eliminate updating some of the
rows if the row already has the new value. As strange as this seems,
something as simple as:
I'm coming up empty on this one and it should be quite simple. Maybe
because it's Friday??
I have 2 tables, Dept and Employee. I want to count the # of employees in
each dept and store it in the corresponding Dept row.
To do the tally it is simply creating a temporary table:
drop table if
I'm planning to use MySQL on a PHP webserver and I like to know how the
minimum memory required for each web connection. Is there any way to
estimate this? Or test for this on my development machine? TIA
Brent
_
Do You Yahoo!?
Get
At 01:17 AM 3/20/2002, you wrote:
An Oracle DB programmer reviewed a query that I wrote and told me that
putting constants at the beginning of the query would make it slower. I
thought I'd go to the experts on MySQL and be told the truth one way of
the other.
Example query:
SELECT
At 10:39 AM 3/18/2002, you wrote:
Greetings,
We're running MySQL 3.23.47 on MacOS X 10.1.2. We've got a rather large
table ( 200,000+ records, 120+ columns) and some simple queries on that
table have pretty inconsistent performance. Due to licensing issues I
can't give an actual example of the
table1 where IndexField1 = 30460203;
Brent
At 12:23 PM 3/18/2002, you wrote:
BD wrote:
At 10:39 AM 3/18/2002, you wrote:
[snip]
Field1 and Field2 are both unsigned integers
IndexField1 is an unsigned integer and is the primary key
select Field1,Field2 from table1 where IndexField1 = 345232
At 11:13 AM 3/16/2002, you wrote:
Hello,
I have since several weeks a problem in mysql. I'm using the 3.23.42
version on MacOSX.
Sometime, the mysql does'nt respond any more to request made by my script
(often PHP), and I have the following error : Table 'mysql.db' doesn't exist.
The MySQL
The eWeek article http://www.eweek.com/article/0,3658,s=708a=23115,00.asp
that benchmark the various databases including MySQL stated:
MySQL's great performance was due mostly to our use of an in-memory query
results cache that is new in MySQL 4.0.1. When we tested without this
cache, MySQL's
At 02:05 AM 3/14/2002, you wrote:
Hi,
I run mysql on windows as well as linux.
table creation and access right are given by java program.
Problem im facing is.,
i create a table with name TEMP
in windows in creates table with name temp and linux TEMP
while giving rights thru GRANTstatement ,
At 11:17 AM 3/14/2002, you wrote:
On Thursday 14 March 2002 9:32 am, BD wrote:
Why or why couldn't MySQL have used case insensitive names in the first
place? Store everything in lower case internally, but allow the user to use
Invoice_Number or invoice_number or INVOICE_NUMBER in the SQL
At 01:02 AM 3/13/2002, you wrote:
Greetings!!!
I would just like to know if the following is normal:
I created a file TRANSACTION_LOG.sql to create an InnoDB table with MULTIPLE
KEYs with one AUTO COLUMN.
The file consists of the following SQL commands:
drop table TRANSACTION_LOG;
create
At 02:59 PM 3/9/2002, you wrote:
I have a column defined as varchar(255) in my MySql-database. Now I
realized that there's not enough room for my inserts. Is there any chance
to change it's datatype to tinytext without making an entirely new
database and inserting data from the old one to it?
What's the best way to timestamp records if records are to be exported and
then re-imported to another web server in a different time zone? Data will
be exported as comma delimited data. All of the combined records should
reflect the same instant in time, and not have the web site in
I have 2 tables, table1 and table1_archive. The archive table has identical
columns as table1 except it has a few extra columns like a log_date,
user_id. I would like to copy the corresponding columns from table1 to
table1_archive. Sounds simple, right?
Example:
insert into table1_archive
At 01:40 PM 3/7/2002, you wrote:
Are there any documented reasons why mysql would only enter certain fields
and skip others?
I am having some strange problems where it seems that data are randomly
dropped from inserts via web forms.
Have posted before stating a possible cache or proxy problem,
At 10:24 PM 3/4/2002 , you wrote:
On Mon, Mar 04, 2002 at 01:47:42PM -0600, BD wrote:
Jeremy,
PMFJI, but has anyone done any testing to see if persistent
connections with MySQL and PHP is actually faster in practice?
What's PMFJU?
Pardon me for jumping in
Without testing, I
At 01:13 PM 3/4/2002 , you wrote:
On Mon, Mar 04, 2002 at 11:19:54AM +0100, Takacs Istvan wrote:
Hi,
We use mysql 1.23.47 as a database for our PHP based accounting
system. We use mysql_pconnect() to get a connection to the DB.
This morning we couldn't log in because mysql sent back a
Craig,
At 10:02 AM 3/3/2002 , you wrote:
In an effort to encrypt private data in my database (national id numbers,
credit card numbers, passwords) I have had to resort to writing code in my
application using the Blowfish algorithm. Is there any facility for doing
this within MySQL (on
Roger,
You can always write your own Round() function and have it
round exactly the way you want.
To round up on a decimal digit 5 use:
mysql select truncate(1.235+0.005,2) rounded;
+-+
| rounded |
+-+
|1.24 |
+-+
1 row in set (0.00 sec)
To round down
At 01:22 AM 3/3/2002 , you wrote:
I need to pull a variable number of fields from a table from the last
inputted fields. For example, instead of doing something like
SELECT * FROM table
I am looking for a way to do something like this(hypothetical, I don't
really know what I should do).
At 03:31 PM 2/27/2002 , you wrote:
Does MySQL 4.01 support Views? And if so, how?
Stephen Cox
Web Development, Webmaster
[EMAIL PROTECTED]
Stephen,
Views were on the list for the 4.1 release so your friend may have
accidentally transposed the .01 and the .1. However this feature,
In case anyone is interested, here is the link for the on going saga of
MySQL AB vs NuSphere court case, soon to be made in a TV mini-series (just
joking).
http://newsforge.com/
Brent
_
Do You Yahoo!?
Get your free @yahoo.com
At 04:38 PM 2/21/2002 , you wrote:
At 22:15 + 2/21/02, DL Neil wrote:
MySQL by Paul DuBoise
Paul monitors this mailing list, so if you have questions concerning what
you've read, he may answer.
You added an e to my name deliberately just to see if I'd reply,
didn't you! :-)
Not really,
At 06:37 AM 2/21/2002 , you wrote:
This off topic, but one of our programmers is interested in NuSphere's PHPEd
development interface and I would liek some feed back from other users.
His interest lies not so much in using for database table modeling, but for
PHP and Perl development. I am well
I'm creating a web application with MySQL, PHP, InnoDB and I need to know
whether I should split the one large table into 3 different tables with
different user privileges defined on each table.
As it stands now, the user logs in by entering a username pw and this is
checked against a
At 08:33 PM 2/16/2002 , you wrote:
Hello
I am new to the subject. I am experimenting in mysql via PHP with a nice
book (PHP and MySQL
Web development). My question is how many queries to mysql, made via PHP,
should considered
ok for efficiency. I know it has much to do with the size of
At 03:26 PM 2/14/2002 , you wrote:
What Rick said is absolutely correct and you probably are obsessing about
something that doesn't matter. But I would venture you are using an
auto-number field as the primary key when you could easily change it to a
function something similar to: set ID =
Bill,
Some databases can use a live result set when retrieving a lot of
records and I really really wish MySQL could do the same. A live result set
does not create a temporary table or use memory to retrieve all the
records. It will grab 50 or so records at a time, and when scrolling
At 08:12 AM 2/8/2002 , you wrote:
Hi, sir/madam:
I'm new to MYSQL. I'm now doing my final year project,
and intend to use mysql as the database system.
I installed the version mysql-3.23.46a-win and
myodbc-2.50.39-nt on my computer for testing.
I would like to download a MYSQL GUI for data
At 10:34 AM 2/8/2002 , you wrote:
Quite often I'd like to duplicate an existing table using SQL. Normally I'd
do a
create table NewTable select * from OldTable;
and if I don't want any data transferred I'll slap on a Limit 0 on the end
of the statement.
This works fine except it doesn't
At 08:12 AM 2/8/2002 , you wrote:
Hi, sir/madam:
I'm new to MYSQL. I'm now doing my final year project,
and intend to use mysql as the database system.
I installed the version mysql-3.23.46a-win and
myodbc-2.50.39-nt on my computer for testing.
I would like to download a MYSQL GUI for data
At 04:21 AM 2/5/2002 , you wrote:
Dear all,
Which of the following SQL queries is faster and better
select thefield from thetable group by thetable
Or
select distinct thefield from thetable?
and WHY?
Best Regards
Hayan
Hayan,
I believe the Select Distinct.. gets translated into a Group
Hery,
You need to get some idea of how many records are being counted.
What I suggest is to put a Limit on your select statement and gradually
increase it.
For example
select count(*) from TABLE_1 where FIELD_1 = XXX_CC limit 10;
If this takes too long, then you know right away
At 01:19 PM 2/4/2002 , you wrote:
2) I use MySQLfront just for a couple of days. I find it very usefull (has
to be written in Delphi) Is this the best freeware for this job or is there
a better one ?
Makis,
MySQLFront is pretty good considering the price. :)
There is
make a first approximation.
Maybe a field where you could copy and paste the
output of show status would
help to alleviate the problems and get us to at
least a second approximation.
I agree that some kind of automation here would be
desirable.
BD wrote:
I sent a reply back
At 03:02 PM 1/30/2002 , you wrote:
At 12:22 PM 1/30/2002 -0600, you wrote:
I sent a reply back to JW about his problem on performance tuning and
came up with I thought a really good idea. But there weren't any bites so
I thought I start it on a new thread.
I'd like to point out that I'm
I have a Win2k machine and want to know if it is practical for development
purposes to run MySQL in a VMWare linux window. Has anyone tried it? Are
there any caveats? TIA
Brent
-
Before posting, please check:
I sent a reply back to JW about his problem on performance tuning and came
up with I thought a really good idea. But there weren't any bites so I
thought I start it on a new thread.
There are a lot of threads about setting up MySQL for the best
performance. This might help to solve the
I have 2 tables, detail and product. The detail table could have millions
of rows. The product table could have a few hundred. I need to know which
products are in the detail table. (It would also be nice to know which
products aren't in the detail table). The problem is it has to be fast,
At 02:31 AM 1/25/2002 , you wrote:
On Thu, Jan 24, 2002 at 05:27:18PM -0600, BD wrote:
I will be putting variable length text into a field (up to 255
characters but typically around 60 characters) and wonder what makes
for faster retrieval? Or does it matter? A field defined as
Varchar
I will be putting variable length text into a field (up to 255 characters
but typically around 60 characters) and wonder what makes for faster
retrieval? Or does it matter? A field defined as Varchar(255) or Text? It
is unlikely this field will be used for searching and it will not be
Displaying timestamp columns are a PIK (pain in keester) if you ask me.g
A Select statement will display the TmeStamp as 2005095105. Now I ask
you, how many users will understand this format?
Why not display TimeStamp in the same format as DateTime? At least then the
TimeStamp is in a
I'm just started using PHP so maybe I'm doing something silly here, but for
some reason I can't create an InnoDb table from within a PHP script.
The same script can create a MYSIAM table just fine when Type=MyISAM, but
when the table type is Type=InnoDb then the query fails. MySQL.Err has
not
Is there any news on the release date for MySQL 4 for Windows?
I feel like I'm sitting by the side of the road waiting for Godot. Is it
ever going to show up?g
Brent
-
Before posting, please check:
When I get a MySQL error, the error message is only partially written to
MySQL.Err file (on WIn2k). If I call the MySQL.Err text file up in an
editor window, only half of the error message is visible. I tried MySLAdmin
flush-logs but that didn't help. How do I get the entire error message
PMFJI,
Do any of the public web archives of the list suit your needs? Have
you seen them?
It's fine for archived messages but too hard to find new messages, say
within the last 7 days. Staying current with MySQL is a lot harder with the
mailing list because you have to keep up with the mail
I'm trying out MySQL-Front on a Win2k machine and it is having a problem
opening some of my InnoDb tables.
I can open any of my InnoDb tables if I explicitly specify the database
name in the Connection window. But if I leave the database name blank I'll
see the database tree in the pane on
For a database cleanup project...
Table A: prod_id, prod_desc
Table B: prod_id, prod_price, (and various other fields)
Would like to come up with a query that will give me a list of all rows in
Table A that don't exist in Table B... I've tried several different ways,
but can't seem to come up
Perfect!
Thanks...
http://www.bustdustr.net
Home Of Radio Free BD
SELECT * FROM tableA left join tableb on tablea.prod_id = tableb.prod_id
where tableb.prod_id is NULL;
-Original Message-
From: BD [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 15 February 2001 15:16
To: [EMAIL
78 matches
Mail list logo