At 05:49 AM 10/21/2011, you wrote:
how to optimize mysql in easy way step, i know indexing, mapping other than
that is any way.
--
Thanks Regards,
P.Benaya Paul
Apart from enrolling in Hogwarts, you may be interested in the
book High Performance MySQL 2nd Edition. It can be found
how to optimize mysql in easy way step, i know indexing, mapping other than
that is any way.
--
Thanks Regards,
P.Benaya Paul
http://www.codeasearch.com
http://www.iwannasearch.com
- Original Message -
From: Benaya Paul benayap...@gmail.com
how to optimize mysql in easy way step, i know indexing, mapping
other than that is any way.
Forsooth, three paths extend before thee. The right way is to learn about mysql
so you know what you're doing. The intermediate
Hi Mike,
Thanks for you help!
Best,
Jia
mos wrote:
Jia,
The code you sent seems to be able to get the job done.
You could try something simpler by executing 2 sql statements instead
of using one. Something like:
create table rmpdata1
select ri.*, mv.* from RItime as ri left join
Thanks for your reply, Mike.
Yes, 13419851 rows were added to rmpdata1. However, 10 minutes seem to
be too long. I run the same join by using SQL procedure in a statistical
software called SAS on a similar machine. It only takes 1 minute and 3
seconds.
Yes, it is a 1:1 relationship between
Jia,
Yes, it is a 1:1 relationship between table RItime and MVtime. However, I
don't get your suggestion, I'd recommend joining the two tables into 1
table so you don't have to join them in the first place. Could you
elaborate that?
Sure but first I have to relate it to my own experience.
Hi Mike,
Thanks for your detailed answer. Now, I understand what you mean. And,
yes, I agree with you that keeping all data in one table works better
for a bunch of 1:1 relationship tables. Actually, this is what I was
trying to do with that query.
Since you mention They all had a 1:1
Jia,
The code you sent seems to be able to get the job done.
You could try something simpler by executing 2 sql statements instead of
using one. Something like:
create table rmpdata1
select ri.*, mv.* from RItime as ri left join MVtime as mv on
(ri.code=mv.code and ri.ndate=mv.ndate));
Hi there,
One simple query took more than 10 minutes. Here is how relevant rows
in the slow query log looks like:
# Time: 090905 10:49:57
# u...@host: root[root] @ localhost []
# Query_time: 649 Lock_time: 0 Rows_sent: 0 Rows_examined: 26758561
use world;
create table rmpdata1 select
How many rows were added to rmpdata1 table? If it is 13.4 million rows then
it is going to take several minutes to join this many rows from the 2 tables.
Is there a 1:1 relationship between the two tables or a 1:Many? If there is
a 1:1 then I'd recommend joining the two tables into 1 table so
temporary table
howevery
I have thousands of queries per second.
How to optimize it?
Anthoer question:
Select * from user where user_id in(id1,id2,id3,id4,.) order by
use_id;
I add index on user_id,but after in,order use temporary table, How to
optimize it?
Thanks
;
I add index on (key1,key2,key3,user_id), this sql use temporary table
however. I have thousands of queries per second. How to optimize it?
Because of the distinct clause, mysql has to remember all of the user_id
values during the query so it can remove duplicates. You do have an index
user_id from user where key1=value and
key2=value2 and key3=value2;
I add index on (key1,key2,key3,user_id), this sql use temporary table
howevery
I have thousands of queries per second.
How to optimize it?
Anthoer question:
Select * from user where user_id in(id1,id2,id3,id4,.) order
Hi,
I have a sql :
Select distinct user_id from user where key1=value and
key2=value2 and key3=value2;
I add index on (key1,key2,key3,user_id), this sql use temporary table
howevery
I have thousands of queries per second.
How to optimize it?
Anthoer question:
Select
hi list,
i have tables that look like this( 10.000 entries) :
id,
timestamp,
value
to get the latest value for each id i have queries like:
select * from tab A where timestamp = (select max(timestamp) from tab B where
B.id=A.id) group by id ;
on a fast system it takes round 4 sec to complet,
On Thu, 2008-07-24 at 14:49 +0200, walter harms wrote:
hi list,
i have tables that look like this( 10.000 entries) :
id,
timestamp,
value
to get the latest value for each id i have queries like:
select * from tab A where timestamp = (select max(timestamp) from tab B where
to get the latest value for each id i have queries like:
select * from tab A where timestamp = (select max(timestamp) from tab B where
B.id=A.id) group by id ;
See Within-group aggregates at
http://www.artfulsoftware.com/infotree/queries.php.
PB
walter harms wrote:
hi list,
i have
Peter Brawley wrote:
to get the latest value for each id i have queries like:
select * from tab A where timestamp = (select max(timestamp) from tab
B where B.id=A.id) group by id ;
See Within-group aggregates at
http://www.artfulsoftware.com/infotree/queries.php.
PB
hi peter,
txh
Kwang Chin Lee wrote:
Hello,
I have several tables storing item information, keyword (mainly for
searching), category and subcategory (also for searching). The query I am
using now is:
SELECT i.*, it.*, ic.*, c.*, cs.*, s.*, st.* FROM item i
LEFT JOIN iteminfo it ON i.id = it.id
Hello,
I have several tables storing item information, keyword (mainly for searching),
category and subcategory (also for searching). The query I am using now is:
SELECT i.*, it.*, ic.*, c.*, cs.*, s.*, st.* FROM item i
LEFT JOIN iteminfo it ON i.id = it.id
LEFT JOIN itemkeyword ik ON i.id
Hello,
I have several tables storing item information, keyword (mainly for searching),
category and subcategory (also for searching). The query I am using now is:
SELECT i.*, it.*, ic.*, c.*, cs.*, s.*, st.* FROM item i
LEFT JOIN iteminfo it ON i.id = it.id
LEFT JOIN itemkeyword ik ON i.id =
I have to tables (on mysql 5.0.22):
Table: shelf
CREATE TABLE `shelf` (
`isbn` varchar(10) NOT NULL default '',
`product_type` char(1) default NULL,
`title` varchar(150) NOT NULL default '',
(...)
PRIMARY KEY (`isbn`),
KEY `publ_date` (`publ_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Hello
Thank you so much. I Just increase the size of some buffers:
# The MySQL server
[mysqld]
default-character-set = cp1251
port= 3306
socket = /x/xx.sock
basedir = /x
datadir = /x/x
log-error = /x/error.log
log-slow-queries = /x/slow.log
Hello.
At first, we should ensure that most time query is spending in the
ordering of the results. What state is SHOW PROCESSLIST reporting
for this query?
Michael Monashev [EMAIL PROTECTED] wrote:
Hello
GP And MySQL uses a filesort algorithm.
How to make the filesort
Hello
GP What state is SHOW PROCESSLIST reporting for this query?
Fulltext initialization
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ http://ryxi.com/
http://gyxe.com/ http://gyxu.com/
http://xywe.com/ http://xyqe.com/
--
MySQL General Mailing List
For
Hello.
Fulltext initialization
Ok, now we exactly know that we should optimize the FULLTEXT part
of your query. I don't know the options which directly affects the speed
of the FULLTEXT searches, so I suggest you to play with key_buffer and
table structure. The ratio of Key_reads to
Hello
GP And MySQL uses a filesort algorithm.
How to make the filesort faster? May be I have to increase size of
some buffers?
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/ http://ryxi.com/
http://gyxe.com/ http://gyxu.com/
http://xywe.com/ http://xyqe.com/
--
Privet.
SELECT SQL_CACHE id, program_name, categor, subcategor, subsubcategor,
subsubsubcategor, source_url, rating, short_description, long_description,
when_modifed+0, author, searched_words, size_in_kb, licence, language,
install, win95, win98, winme, winnt, win2000, winxp, wince,
Privet!
Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
statements, amount of RAM, most problematic queries (use mysqldumpslow
utility to find them) include tables' definitions (use SHOW CREATE
TABLE).
Michael Monashev [EMAIL PROTECTED] wrote:
Hello,
I have
Hello
GP Send to the list the output of 'SHOW VARIABLES' and 'SHOW STATUS'
GP statements, amount of RAM, most problematic queries (use mysqldumpslow
GP utility to find them) include tables' definitions (use SHOW CREATE
GP TABLE).
SHOW VARIABLES;
back_log50
basedir /*/
Hello,
I have 200-300 kb slow log daily with fulltext queries only :-( All
queries using fulltext indexes. I use huge mysql cofig (huge.cfg).
What can I change in the mysql configuration for better performance?
Sincerely,
Michael,
http://xoib.com/ http://3d2f.com/
http://qaix.com/
Well, it seems you've helped solve my problem, Shawn...here we go:
first off, sorry about the text wrapping and the sloppy sql...I do get
lazy sometimes...I am a programmer :P
you asked how fast this runs:
SELECT ind_first_name
, ind_last_name
, fam_phone
,
Kevin Weslowski [EMAIL PROTECTED] wrote on 05/26/2005 02:20:46 AM:
Hi,
I've got a query that I believe should be giving me better performance
than it is, since I've put indexes on all the appropriate columns...I'll
include as much info as I can for anyone wishing to help me optimize the
Hi,
I've got a query that I believe should be giving me better performance
than it is, since I've put indexes on all the appropriate columns...I'll
include as much info as I can for anyone wishing to help me optimize the
query/tables/indicies...thanks in advance for any assistance...
the
I have, to make it clear, 2 many-to-many relationships for table person:
PersonPerson_Club ClubPerson_Creditcard
CreditCard
----- -
-
On 24-Mar-2004 Henrik Schröder wrote:
snip
No, I did not choose the sort order. No, I can not change the values
used.
Yes, it has to be this sort order. :-)
Best would be to use ENUM( '1','2','0') ...
But otherwise --
(membershiptype % 2) DESC, membershiptype DESC
-- or --
Hi all,
I have a table of users which contains a lot of information, and also a
column called membershiptype which can either be 0, 1, or 2. This table is
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be 0-1-2, but instead 1-2-0.
Does the where clause contain the order by columns? If not, then you will have
a filesort no matter what.
On Wed, 24 Mar 2004, Henrik Schröder wrote:
Hi all,
I have a table of users which contains a lot of information, and also a
column called membershiptype which can either be 0, 1, or 2.
From: Henrik Schröder [EMAIL PROTECTED]
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be 0-1-2, but instead 1-2-0.
One thing you could do (not very neat IMHO) is to JOIN this query with a
table that contains membershiptype and a
Let me rephrase my statement. Does the index being used include the order by
columns? If not, the optimizer will not be able to perform both row selection
and row ordering in the same pass.
On Wed, 24 Mar 2004, Victor Pendleton wrote:
Does the where clause contain the order by columns? If not,
On Wed, 24 Mar 2004 15:47:46 +0100, Henrik Schröder wrote:
I have a table of users which contains a lot of information, and
also a column called membershiptype which can either be 0, 1, or 2.
This table is used a lot in various searches, and all results
should be ordered by membership type,
-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: den 24 mars 2004 16:22
To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]'
Subject: Re: How to optimize ugly order by?
Let me rephrase my statement. Does the index being used include the order by
columns? If not, the optimizer
Hemert [mailto:[EMAIL PROTECTED]
Sent: den 24 mars 2004 16:12
To: [EMAIL PROTECTED]
Subject: Re: How to optimize ugly order by?
From: Henrik Schröder [EMAIL PROTECTED]
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be 0-1-2
the biggest problem, right?
/Henrik
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: den 24 mars 2004 16:22
To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]'
Subject: Re: How to optimize ugly order by?
Let me rephrase my statement. Does
I assume you are just trying to make things more readable. As far as I
know, MySQL does not support custom sort orders, except when a field is
an enum or a set. Even then, that's not a custom order since your still
sorting on the underlying values.
You could do something like this for
Henrik Schröder wrote:
Hi all,
I have a table of users which contains a lot of information, and also a
column called membershiptype which can either be 0, 1, or 2. This table is
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be
Can you do:
SELECT
IF(membershiptype = 0, 3, membershiptype) AS s1
FROM...
ORDER BY s1
Shouldn't that work? Basically if mtype is 0 return 3 otherwise return
the membership type... and sort by that field.
lemme know if that works or if I'm dead wrong ;)
Henrik Schröder wrote:
Hi all,
I have a table which contains many items. Each item consists of an
scriptID, several attributes, and an optional field - a parent (called
dupeitem in the database) item. Most all of these fields are strings -
even the IDs which consist of a combination of letters and numbers. Items
which have a
Hi.
On Mon 2002-07-08 at 17:49:03 -0700, [EMAIL PROTECTED] wrote:
Hello,
I have _a lot_ of http log data to throw into a mysql db (currently over 1.5
billion rows). New data is coming in all the time, so I don't want to lock
myself into one set of big tables that are over 100 gigs each.
Hello,
I have _a lot_ of http log data to throw into a mysql db (currently over 1.5
billion rows). New data is coming in all the time, so I don't want to lock
myself into one set of big tables that are over 100 gigs each. I'd rather
arrange this data into smaller chunks, then merge the tables
Hi there,
after reading a nice articel on normalisation, I did split my tables and now
I am suffering performance lost.
this query (finding out the amount of user which have posted an article on
germany) takes 4.5 s:
SELECT count(DISTINCT user_recommending)
FROM recommendations t
INNER JOIN
[EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, May 20, 2002 1:48 PM
Subject: Join takes way too long. How to optimize this smal query?
Hi there,
after reading a nice articel on normalisation, I did split my tables and
now
I am suffering performance lost.
this query (finding out
Hello.
On Thu, Apr 25, 2002 at 01:49:39PM +0200, [EMAIL PROTECTED] wrote:
[...]
mysql show index from cpu;
+---++--+--+-+---+-+--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Hi!
I have this table :
mysql desc cpu;
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
How to optimize this query:
SELECT col1, col2, MAX(col3) FROM Table WHERE col42 AND col5 = 2 GROUP BY
col3 LIMIT 1;
The best that i figured out is to set the index on col3.
I was unable to find out the working combination for multi-column indexing.
Please advice
In the last episode (Dec 28), Goran Krajacic said:
How to optimize this query:
SELECT col1, col2, MAX(col3) FROM Table WHERE col42 AND col5 = 2 GROUP BY col3
LIMIT 1;
Try a compound index on (col5,col4), or if you really want speed,
(col5,col4,col3,col2,col1), which will let mysql use
Hi!
I have a problem! Too many connections error in myODBC.
I am wondering now how to optimize the code...
What is better, to make 14 open.comm and close.conn on a web page or to open
once and close once?
Asked differently, is it better to connect to database one and be connected
until all
Every connection will carry some overhead cost. So you'll have to
connect as few times as possible.
Jerry wrote:
J: Hi!
J:
J: I have a problem! Too many connections error in myODBC.
J:
J: I am wondering now how to optimize the code...
J:
J: What is better, to make
First let me explain what I am trying to do. I want to archive many many
messages into a mysql database. The format is pretty simple... either the
message is a parent, or it is a reply. The problem is that some of the
queries are taking 5-10 seconds, which is not acceptable for my application.
59 matches
Mail list logo
