Re: slow select when using VIEW

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

RE: SLOW select when ordering

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: >+---+---

Re: slow select... where... order by

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 de

Re: slow select... where... order by

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 un

Re: Slow select

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 l,huvud_

Re: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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

RE: Slow select query, need some clues to speed it up please ...

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%' | >

Re: Slow select query, need some clues to speed it up please ...

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

RE: Slow select query, need some clues to speed it up please ...

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

RE: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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

RE: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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:

Re: Slow select query, need some clues to speed it up please ...

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

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 LIKE '%John%' OR Field5 LIKE > '% John%' ) ORDER BY Field6 LIMIT 0,20; query 2: > SELECT Field1,

Re: Slow select query, need some clues to speed it up please ...

- 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

Re: Slow select query, need some clues to speed it up please ...

]> 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

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 ... >

Re: Slow select query, need some clues to speed it up please ...

:( 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

Re: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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 >

Re: Slow select query, need some clues to speed it up please ...

- 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

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(); +--+--+--++---

Re: Slow select query, need some clues to speed it up please ...

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, Aug

Re: Slow select query, need some clues to speed it up please ...

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

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> 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;

Re: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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

Re: Slow select query, need some clues to speed it up please ...

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

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 ../data/jeuxvideo/MyTable David sql,query --

Re: Slow select query, need some clues to speed it up please ...

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

RE: Slow Select count(*) - Second Post

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 Se

Re: Slow Select count(*) - Second Post

Hello > Is there any way to re-write SQL to force the optimiser to use a certain > index? SELECT foo FROM bar USE INDEX baz WHERE ... See the manual at . cheers -- TS - Before posting,

RE: Slow Select count(*) - Second Post

Thanks for your replies. Patrick: I will try the combined index, and see if it takes too much disk space. Most queries return within 2 seconds, which is acceptable for me. Therefore I'm not too concerned about the speed increase in using a combined index as such. What I am concerned about

RE: Slow Select count(*) - Second Post

e often used in WHERE clauses. Patrick -Message d'origine- De : Robin Keech [mailto:[EMAIL PROTECTED]] Envoyé : mardi 20 février 2001 16:15 À : '[EMAIL PROTECTED]' Objet : RE: Slow Select count(*) - Second Post Thanks for your response, What benefit would that give me? Wo

RE: Slow Select count(*) - Second Post

select against the queue_index take so long? Thanks for your help in advance, Robin -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 ind