Hi
I have created the following 2 views:
CREATE VIEW `cpes_noise_num` AS
SELECT cpes_dsl_line_stats.id_cpes,
sum(IF(cpes_dsl_line_stats.snr_downstream
(SELECT snr_downstream FROM admin_configs WHERE admin_id =0),1,0)) AS
snr_downstream, sum(IF((cpes_dsl_line_stats.bit_errors /
In the last episode (Jun 10), Yariv Omer said:
I have created the following 2 views:
CREATE VIEW `cpes_noise_num` AS
[ big view]
CREATE VIEW `my_connect` AS
[ big view joining on cpes_noise_num ]
when I am trying to do something like:
SELECT count(*) from my_connect
It takes 1 minute
Jigal van Hemert napsal(a):
| 1 | SIMPLE | o | ALL| NULL | NULL|
NULL | NULL| 20402 | Using temporary; Using
filesort |
Well, this is a clue IMHO... no suitable indexes in o could be found
to use
in this query...
yea, but I use a lot of indexes
Pavel Novk wrote:
snip
yea, but I use a lot of indexes and I don't know how to set it better.
Bellow there are create table info so could you tell me what do you
think would be bad, please?
To begin with, you have redundant indexes. Unnecessary indexes slow you
down.
CREATE TABLE `order_acl` (
Pavel Novak wrote:
Hello,
I am clueless of this query, becaouse it's very slow - between 30 and
60 seconds:
mysql SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS
serialid
- FROM orders o
- LEFT JOIN editor e1 ON o.createdby = e1.id
- LEFT JOIN editor e2 ON o.changedby
Michael Stassen napsal(a):
Is this really your query? The LEFT JOINed tables, editor, customer,
product, brand, calendar, and partner do not contribute to the output
(i.e. none of their columns are selected), nor are they used to
determine which rows to consider (i.e. they don't appear in the
Hello,
I am clueless of this query, becaouse it's very slow - between 30 and
60 seconds:
mysql SELECT DISTINCT o.id AS id, o.orderid AS orderid, o.serialid AS
serialid
- FROM orders o
- LEFT JOIN editor e1 ON o.createdby = e1.id
- LEFT JOIN editor e2 ON o.changedby = e2.id
- LEFT
From: Pavel Novak
| 1 | SIMPLE | o | ALL| NULL | NULL|NULL
| NULL| 20402 | Using temporary; Using filesort |
Well, this is a clue IMHO... no suitable indexes in o could be found to use
in this query...
Since you use a lot of columns from
Also note that the server is pretty fast, dual 2 GHz with 2GB of RAM. Using the
my-huge.cnf settings.
Also... I forgot to mention [EMAIL PROTECTED] I'm using:
mysql 4.0.20-standard-log Official MySQL RPM
And here is the EXPLAIN on the select portion of the slow query is:
Dave,
It's best to keep threads on the list. On average, you'll get better and
faster responses when all the experts can see your question. Also, others
can benefit from the answers when they are on the list.
See my comments below.
Critters wrote:
Thanks for your response, here is some more
Hi
I have a database with just over 10,000 records. with the following structure:
id, compid, name, score, and about 10 other fields
I have indexed id, compid, score
about 10 records out of the 10,000 have a compid of 2
when i do
select * from table where compid = 2
it was slow until i
Critters wrote:
Hi
I have a database with just over 10,000 records. with the following structure:
id, compid, name, score, and about 10 other fields
I have indexed id, compid, score
about 10 records out of the 10,000 have a compid of 2
when i do
select * from table where compid = 2
it was slow
On Mon, Sep 08, 2003 at 05:50:15PM +0200, Bertil Wergelius wrote:
I have an application, where I use the following select-query:
select h.huvud_id,datum, start_tid, stopp_tid,l.lokal_beteckning,
namn,adress,rubrik,amne,grupp,kursnamn,lr.signatur
from huvud_schema as h,lokaler as
I have an application, where I use the following select-query:
select h.huvud_id,datum, start_tid, stopp_tid,l.lokal_beteckning,
namn,adress,rubrik,amne,grupp,kursnamn,lr.signatur
from huvud_schema as h,lokaler as l,huvud_amnen as ha,amnen as a,
grupper as g,huvud_grupper as hg,kurser as
Hi!
I have posted this message yerstaday, but apperantly
did not give enough information for reader to figure out
what's going on. So, i am reposting it with more information.
I have the following table:
create table law_words (
l_id int unsigned not null,
w_id int unsigned not null,
On Wed, 12 Feb 2003 15:50:29 +0300
Artem Koutchine [EMAIL PROTECTED] wrote:
Hi!
I have posted this message yerstaday, but apperantly
did not give enough information for reader to figure out
what's going on. So, i am reposting it with more information.
I have the following table:
But you're not using the word search engine, which would be the full
text index. I'm not sure what you mean by morphology. MySQL 4 full
text searches support a lot of things, including using * as the
truncation operator.
Aside from that, perhaps it's the speed of your processor that
[EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, February 12, 2003 4:57 PM
Subject: Re: Very slow select query with IN(...)
But you're not using the word search engine, which would be the full
text index. I'm not sure what you mean by morphology. MySQL 4 full
text searches support a lot
On Wed, 12 Feb 2003 15:50:29 +0300
Artem Koutchine [EMAIL PROTECTED] wrote:
Hi!
I have posted this message yerstaday, but apperantly
did not give enough information for reader to figure out
what's going on. So, i am reposting it with more information.
I have the following table:
Entaltsev
Cc: [EMAIL PROTECTED]
Subject: Re: Slow select query, need some clues to speed it up please
...
ME In this case...
ME Can you try again?
Sure.
query 1 :
SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable
WHERE
Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4
BS Have you tried the command : EXPLAIN SELECT select_options
BS (http://www.mysql.com/doc/en/EXPLAIN.html). Maybe it can help you to see
BS what Indexes are used ...
Yep i do that but varchar and text fields aren't index so ...
I'm also having a look to FULLTEXT index, but i think that the
What do you mean varchar and text fields are not index?
alter table mytable add index (myvarchar, mytext(100))
One last resort that may work for you is to try the regexp instead of
LIKE. The functionality of regexps for me has at best been
unpredictable, sometimes it is faster, sometimes at
David,
| | As I understand...after removing Field5 LIKE '%John'
| | condition the query works much faster.
|
| Nop, sorry i'm not clear, the query works mush slower ...
This will be fast if you have the right index--
field5 like 'John%'
These will NOT be fast
From: Shashank Tripathi [EMAIL PROTECTED]
This will be fast if you have the right index--
field5 like 'John%'
These will NOT be fast --
field5 like '%John%'
field5 like '%John'
Putting a wildcard character (%) before the term will avoid the index.
Yep, and it's for that point that
Hi David,
| Why not regexp but this is not i mean, in fact
| where CONTENT like '%word1%'
| or CONTENT like '%word2%'
| or CONTENT like '%word3%'
| shoud be :
| where CONTENT1 like '%word%'
| or
From: Shashank Tripathi [EMAIL PROTECTED]
| Why not regexp but this is not i mean, in fact
| where CONTENT like '%word1%'
| or CONTENT like '%word2%'
| or CONTENT like '%word3%'
| shoud be :
| where CONTENT1
Hi,
- Original Message -
From: Shashank Tripathi [EMAIL PROTECTED]
To: 'David Bordas' [EMAIL PROTECTED]; 'BRACHET,STEPHAN
(Non-HP-France,ex1)' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, August 28, 2002 12:25 PM
Subject: RE: Slow select query, need some clues to speed
Hi all,
I've discovered that we have a select query that blocked all others query to
this table.
99% of query used indexs, no join ...
But this one is a problem :
SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE
Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR
As first step, try to optimize table with help of
OPTIMIZE TABLE MyTable
command.
Any progress?
Best regards,
Mikhail.
- Original Message -
From: David BORDAS [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 12:34 PM
Subject: Slow select query, need some clues
ME As first step, try to optimize table with help of
ME OPTIMIZE TABLE MyTable
ME command.
ME Any progress?
Already done something like optimize :
myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable
David
sql,query
: Tuesday, August 27, 2002 1:30 PM
Subject: Re: Slow select query, need some clues to speed it up please ...
ME As first step, try to optimize table with help of
ME OPTIMIZE TABLE MyTable
ME command.
ME Any progress?
Already done something like optimize :
myisamchk -v -a -S --sort-records=1
PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 1:34 PM
Subject: Re: Slow select query, need some clues to speed it up please ...
ME As first step, try to optimize table with help of
ME OPTIMIZE TABLE MyTable
ME command.
ME Any progress?
Already done something like optimize
DB Already done something like optimize :
DB myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable
ME And? No result?
Some good result but nothing enough good :(
I'll have a look to fulltext search, perhaps i'll find something good.
I know that fulltext search only search for full word
From: Franz, Fa. PostDirekt MA [EMAIL PROTECTED]
KF an Index wouldnt help much , because of the 'LIKE %... '.
KF If a wildcart is at the beginning of the search-string , an Index
KF cannot help much.
Ok as i thought, index text field is a bad thing ...
KF You have to think about why are there
From: Andrew Izsof [EMAIL PROTECTED]
DB I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5
LIKE
DB '%John')' part is the problem.
AI The formula : '%John%' always sweeps through all of the records, because
it
AI can't utilize any indexes, sorting, etc. But if you leave the
From: Mikhail Entaltsev [EMAIL PROTECTED]
ME Check the query plan
ME EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM
ME MyTable WHERE
ME Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%'
OR
ME Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20;
| table
Pada Tue, 27 Aug 2002 12:34:32 +0200
David BORDAS [EMAIL PROTECTED] menulis :
Hi all,
I've discovered that we have a select query that blocked all others query to
this table.
99% of query used indexs, no join ...
But this one is a problem :
SELECT
Can you send result of the query, please:
select * from MyTable PROCEDURE ANALYSE();
Mikhail.
- Original Message -
From: David Bordas [EMAIL PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 2:13 PM
Subject: Re: Slow select query
- Original Message -
From: Mikhail Entaltsev [EMAIL PROTECTED]
ME Can you send result of the query, please:
ME select * from MyTable PROCEDURE ANALYSE();
Here you are :
mysql select * from MyTable PROCEDURE ANALYSE();
- Original Message -
From: Dicky Wahyu Purnomo [EMAIL PROTECTED]
DB SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable
WHERE
DB Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%'
OR
DB Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20;
DWP have you
: Re: Slow select query, need some clues to speed it up please ...
- Original Message -
From: Mikhail Entaltsev [EMAIL PROTECTED]
ME Can you send result of the query, please:
ME select * from MyTable PROCEDURE ANALYSE();
Here you are :
mysql select * from MyTable PROCEDURE ANALYSE
From: Mikhail Entaltsev [EMAIL PROTECTED]
ME IMHO the problem is in this condition
ME ...Field5 LIKE '%John'...
ME Can you remove it from query and try again?
In fact it was Field5 LIKE '%John%',
but Field5 LIKE '%John' and Field5 LIKE '%John%' don't change query
speed at all except about
actually need this condition? ;)
Best regards,
Mikhail.
- Original Message -
From: David Bordas [EMAIL PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 3:31 PM
Subject: Re: Slow select query, need some clues to speed it up please
From: Mikhail Entaltsev [EMAIL PROTECTED]
As I understand...
After removing Field5 LIKE '%John' condition the query works much
faster.
Nop, sorry i'm not clear, the query works mush slower ...
But it is different query... :)
I mean that queries return different results.
Yep ...
On other
be occasionally anybody run another big query in parallel? in other case
I have no idea... :(
Mikhail.
- Original Message -
From: David Bordas [EMAIL PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 4:28 PM
Subject: Re: Slow select query
ME In this case...
ME Can you try again?
Sure.
query 1 :
SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable
WHERE
Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%'
OR Field5 LIKE
'% John%' ) ORDER BY Field6 LIMIT 0,20;
query 2:
SELECT
PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 5:16 PM
Subject: Re: Slow select query, need some clues to speed it up please ...
ME In this case...
ME Can you try again?
Sure.
query 1 :
SELECT Field1,Field2,Field3,Field4,Field5,Field6
]
- Original Message -
From: David Bordas [EMAIL PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, August 27, 2002 6:16 PM
Subject: Re: Slow select query, need some clues to speed it up please ...
ME In this case...
ME Can you try again?
Sure
Hi List, I'm new to the list
and I have a BrainTeaser for all of you.
I have yet lots to learn about sql and mySQL so any suggestions are very welcome!
Thanx in advance
Hannes
I have following select over a Database that I cannot setup myself, but have to take
on as is:
SELECT
: Wednesday, March 14, 2001 6:29 PM
To: [EMAIL PROTECTED]
Subject:Very (excruciatingly) slow select
Hi List, I'm new to the list
and I have a BrainTeaser for all of you.
I have yet lots to learn about sql and mySQL so any suggestions are very
welcome!
Thanx in advance
Hannes
I
PROTECTED]; [EMAIL PROTECTED]
Subject:RE: Very (excruciatingly) slow select
Hi Hannes
Several approaches to achieve your work. One is upgrade your resource
i.e.
CPU, memory... I do not take this. Another is check all columns used
inside
WHERE clause whether they are indexed. If not,
I see in your 'Explain's, the 2 queries use different indexes, the fast
one uses soc_date_idx, and the slower one uses q_idx. The trick,
perhaps is to force
soc_date_idx to be used in the 2nd case. (Adding ORDER BY soc_date might
do it, 'soc_date=X and (queue_id=Y and server_id=Z) ) might do
-Original Message-
From: Patrick FICHE [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 14:26
To: Robin Keech
Subject: RE: Slow Select count(*) - Second Post
Hi,
What do you think of creating a combined index containing fro example
soc_date, server_id, queue_id ) in this order
in WHERE clauses.
Patrick
-Message d'origine-
De : Robin Keech [mailto:[EMAIL PROTECTED]]
Envoy : mardi 20 fvrier 2001 16:15
: '[EMAIL PROTECTED]'
Objet : RE: Slow Select count(*) - Second Post
Thanks for your response,
What benefit would that give me? Would a combined index be faster? I
the q_idx and see what that gives me.
Is there any way to re-write SQL to force the optimiser to use a certain
index?
Thanks again
Robin
-Original Message-
From: Jarmo Paavilainen [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 15:50
To: Robin Keech
Subject: SV: Slow Select count
Thanks for all your help.
This has got the query time down to 5 seconds from 2 minutes!!
Much appreciated everyone.
Robin
-Original Message-
From: Tibor Simko [mailto:[EMAIL PROTECTED]]
Sent: 20 February 2001 16:46
To: Robin Keech
Cc: '[EMAIL PROTECTED]'
Subject: Re: Slow Select count
Hi,
Can anyone please explain the following?
I do two select count(*)'s from a big table, (21,000,000 + rows 6.5 Gb).
All columns in the where are indexed. The first select is very quick, the
second very slow.
The ONLY difference is the value of one column variable (queue_id), which is
an
57 matches
Mail list logo
