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
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 :
> | " wher
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%'
| >
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 p
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
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
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
o: Mikhail 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 (F
PROTECTED]
[EMAIL PROTECTED]
- 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:
From: "David Bordas" <[EMAIL 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
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 Field1,
- Original Message -
From: "David Bordas" <[EMAIL PROTECTED]>
To: "Mikhail Entaltsev" <[EMAIL PROTECTED]>
Sent: Tuesday, August 27, 2002 4:55 PM
Subject: Re: Slow select query, need some clues to speed it up please ...
> ME> :( That's str
]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, August 27, 2002 4:28 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
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 ...
>
:(
So...
question: Do you 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
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 excep
OTECTED]>
Sent: Tuesday, August 27, 2002 2:49 PM
Subject: 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 >
- 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> h
- 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();
+--+--+--++---
ust 27, 2002 2:13 PM
Subject: Re: Slow select query, need some clues to speed it up please ...
> From: "Mikhail Entaltsev" <[EMAIL PROTECTED]>
>
> ME> Check the query plan
> ME> EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM
> ME> MyTab
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 Field1,Field2,Field3,Field4
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;
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 lea
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 a
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 wor
sage -
From: "David Bordas" <[EMAIL 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 T
ntaltsev" <[EMAIL PROTECTED]>
Sent: 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?
&g
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
--
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 clu
29 matches
Mail list logo
