Tello is right ! Moreno, and I sugest that: if you want query by
"SUBSTR(ISN,2)" ,you should create a field named like "isnInfo", and
save SUBSTR(ISN,2) to this filed when you insert . don't forget create a
index on this field .
于 12-10-9 下午10:04, Andrés Te
STATUS
EXPLAIN SELECT (with substitutions filled in)
> -Original Message-
> From: Andrés Tello [mailto:mr.crip...@gmail.com]
> Sent: Tuesday, October 09, 2012 7:04 AM
> To: Adrián Espinosa Moreno
> Cc: mysql@lists.mysql.com
> Subject: Re: Slow queries / inserts InnoDB
>
You are forcing mysql to do full table scans with the substr...
Use explain to see that you aren't using any index.
Avoid the use of substr in the where clause, by splitting your data, index
that field and do you query over that field.
That is why your query is so slow.
the slow insert, is du
2010/3/19 Olav Mørkrid
> Dear MySQL forum.
>
> I have performance problems when using "left join x" combined with
> "where x.y is null", in particularily when combining three tables this
> way.
>
With a left join, particularly when you're using *is (not) null*, you can't
use index selecting on y
eally sure what to look for yet (Rather new to MySQL and hope google
>> will
>> have some answers J )
>>
>>
>>
>>
>>
>> Thank you
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> From: John Daisley [mail
some of them now but not
> really sure what to look for yet (Rather new to MySQL and hope google will
> have some answers J )
>
>
>
>
>
> Thank you
>
>
>
>
>
>
>
>
>
> From: John Daisley [mailto:mg_s...@hotmail.com]
> Sent: 23 February 2010 10
some answers J )
Thank you
From: John Daisley [mailto:mg_s...@hotmail.com]
Sent: 23 February 2010 10:24 AM
To: machi...@rdc.co.za; mysql@lists.mysql.com
Subject: RE: slow queries not being logged
> From: machi...@rdc.co.za
> To: mysql@lists.mysql.com
> Subject: slo
> From: machi...@rdc.co.za
> To: mysql@lists.mysql.com
> Subject: slow queries not being logged
> Date: Tue, 23 Feb 2010 09:59:13 +0200
>
> Good day all
>
>
>
> I hope you can assist me with this one...
>
>
>
> We have a client where the slow query log wa
D Hill schrieb:
On Thu, 24 Apr 2008 at 08:58 +0200, [EMAIL PROTECTED] confabulated:
D Hill schrieb:
I have something I am trying to resolve with an over abundant number
of slow queries. Perhaps it is because of some additional indexes
needed. As soon as I enabled the option
'log_queries_no
On Thu, 24 Apr 2008 at 10:16 -0400, [EMAIL PROTECTED] confabulated:
On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel
<[EMAIL PROTECTED]> wrote:
IMHO not in this case, cause it is just a simple "WHERE field IN ()"
I'm pretty sure that just looks like a bunch of ORs to MySQL. If it
didn't us
On Thu, Apr 24, 2008 at 2:54 AM, Sebastian Mendel
<[EMAIL PROTECTED]> wrote:
> IMHO not in this case, cause it is just a simple "WHERE field IN ()"
I'm pretty sure that just looks like a bunch of ORs to MySQL. If it
didn't use the index with OR, it won't use it with IN.
What usually works is to
On Thu, 24 Apr 2008 at 08:58 +0200, [EMAIL PROTECTED] confabulated:
D Hill schrieb:
I have something I am trying to resolve with an over abundant number of
slow queries. Perhaps it is because of some additional indexes needed. As
soon as I enabled the option 'log_queries_not_using_indexes =
D Hill schrieb:
I have something I am trying to resolve with an over abundant number of
slow queries. Perhaps it is because of some additional indexes needed.
As soon as I enabled the option 'log_queries_not_using_indexes = 1' in
the configuration file, I started getting messages relating to
Perrin Harkins schrieb:
On Wed, Apr 23, 2008 at 9:22 PM, D Hill <[EMAIL PROTECTED]> wrote:
Can anyone shed some light if I should index wite_desc to speed things up?
No, since you don't use that column at all. If you're not on MySQL 5,
upgrading to MySQL 5 will help. Otherwise, you're best
On Wed, Apr 23, 2008 at 9:22 PM, D Hill <[EMAIL PROTECTED]> wrote:
> Can anyone shed some light if I should index wite_desc to speed things up?
No, since you don't use that column at all. If you're not on MySQL 5,
upgrading to MySQL 5 will help. Otherwise, you're best bet is to
rewrite the quer
On 8/17/06, Chris <[EMAIL PROTECTED]> wrote:
> Unfortunately didn't that help, it leads to:
> ++-+---+---+---
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows| Extra
>
Unfortunately didn't that help, it leads to:
++-+---+---+---
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows| Extra
|
++-+---+---+---
| 1 |
On 8/17/06, Chris <[EMAIL PROTECTED]> wrote:
Jon Molin wrote:
> Hi list
>
> I have 5 tables:
>
> words (word_id int auto_increment, word varbinary(40)) (has ~3.5M
> rows) with the keys:
> PRIMARY KEY (`word_id`),UNIQUE KEY `word_ind` (`word`)
>
> phrases (phrase_id int auto_increment, phrase var
Jon Molin wrote:
Hi list
I have 5 tables:
words (word_id int auto_increment, word varbinary(40)) (has ~3.5M
rows) with the keys:
PRIMARY KEY (`word_id`),UNIQUE KEY `word_ind` (`word`)
phrases (phrase_id int auto_increment, phrase varbinary(100)) (has
~11M rows) with the keys:
PRIMARY KEY (`p
>
>
> I have a question regarding the slow queries log, and queries not using
> index.
>
> I have a small table, with say 10 entries, like that :
> ID | Element
> -
> 1 | One
> 2 | Two
> 3 | Three
> 4 | Four
> 5 | Five
> 6 | Six
> 7 | Seven
> 8 | Eight
> 9 | Nine
> 10 | Ten
>
> I w
Hello.
There could be a lot of reasons for such a delay. First, you
should switch to bulk inserts and perform all operation as a single
transaction. Avoid usage of the autoextended or per-table tablespaces.
Are you able to upgrade? There could be some performance improvements
in the newer ve
Thanks! Explain and InnoDB monitor were exactly what I needed to
diagnose and fix the problem! In case you were curious, the issue was
that the statement I was expecting to run was not the statement that
was running, but the first hundred and some-odd characters in both
were the same. Using the mon
Yes, indexes slow down inserts (or updates that change the value of a
column that is indexed).
Also, remember that MySQL only uses one index per per table in a query.
So if there are some columns in your table that are indexed, but,
1) Have poor cardinality (number of distinct values - low card
me in this, it is very urgent.
Thanks,
Narasimha
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 04, 2005 1:11 PM
To: mysql@lists.mysql.com
Subject: Re: Slow queries, why?
Hello.
> We're running MySQL 4.11 on a machine with 2GB memory, the
Hello.
> We're running MySQL 4.11 on a machine with 2GB memory, the table is
> InnoDB with a compound primary key, and additional indexes on all rows
> with searchable options in the API. Any generic advice or admin tools
> would be great.
Use EXPLAIN to determine how efficient your in
on 5/3/05 7:25 PM, Joseph Cochran at [EMAIL PROTECTED] wrote:
> So here's my situation: we have a database that has a table of about 5
> million rows. To put a new row into the table, I do an INSERT ...
> SELECT, pulling data from one row in the table to seed the data for
> the new row. When there
disadvantages of losing the data on system shutdown.
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 10, 2005 1:43 PM
To: Bob O'Neill
Cc: mysql@lists.mysql.com
Subject: Re: Slow queries only the first time
On Mar 10, 2005, at 11:20 AM, Bob O&
On Mar 10, 2005, at 11:20 AM, Bob O'Neill wrote:
Hello. I am wondering why some of my queries are slow on the first
run, but
speedy on subsequent runs. They are not being query cached, as I have
query_cache_type set to DEMAND. Is it something as simple as pulling
the
data into RAM from disk,
Most likely it's the OS cache caching all those disk
segments in memory. Also in InnoDB, MySQL uses the
Buffer Pool Size to cache data pages in addition to
the OS cache.
If you're running ona Windows machine, you can easily
tell what's going on by opening up Performance Monitor
and watching Pages/
I recently had a similar problem, however you may find that its more of a
case of correctly indexing your tables.
Yyou should look for the tables which need indexing, I enabled the
slow-query-log as well as enabling of logging of queries which didn’t use
indexes and found some which did some very
[snip]
# Time: 050107 17:40:41
# [EMAIL PROTECTED]: zencarttest[zencarttest] @ [xxx.xxx.xxx.xxx]
# Query_time: 13 Lock_time: 0 Rows_sent: 148 Rows_examined: 1567270
use zencarttest;
select distinct m.manufacturers_id, m.manufacturers_name from
zen_manufacturers m
left join
--Original Message-
From: BD
Sent: Friday, January 07, 2005 11:58 AM
To: Tom Crimmins
Cc: mysql@lists.mysql.com
Subject: RE: Slow queries, need advice on how to improve; key_buffer -
zen-cart?
OK thanks Tom,
The application I am using for the site is www.zen-cart.com so I'm not sure
I can do
TYPE=MyISAM |
| zen_products_to_categories | CREATE TABLE `zen_products_to_categories` (
`products_id` int(11) NOT NULL default '0',
`categories_id` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`,`categories_id`)
) TYPE=MyISAM |
+-
-
Friday, January 07, 2005 11:21 AM
To: Tom Crimmins
Cc: mysql@lists.mysql.com
Subject: RE: Slow queries, need advice on how to improve; key_buffer?
Hi Tom,
OK thanks I just added the set-variable = key_buffer = 64M line to my my.cnf
file and at least I got no errors and the MySQL server restarted
the mean time?
BD
-Original Message-
From: Tom Crimmins [mailto:[EMAIL PROTECTED]
Sent: Friday, January 07, 2005 12:08 PM
To: BD
Cc: mysql@lists.mysql.com
Subject: RE: Slow queries, need advice on how to improve; key_buffer?
[snip]
I tried to improve MySQL speed/performance by adding key_buff
[snip]
I tried to improve MySQL speed/performance by adding key_buffer=50M to my
my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a "0
DB_ERROR_NOT_CONNECTED" error when visiting my php-MySQL web application via
http. When I removed above and then restarted MySQL the error goes away.
ed both 64M and just 64. 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 MySQL speed/performance by adding key_buffer=50M to my
my.cnf file for [mysqld]. When I restarted MySQL, I'm getting a "0
DB_ERROR_NOT_CONNECTED" error when visiting my php-MySQL web application via
http. When I removed above and then restarted MySQL the error goes away.
If you are sorting the result, setting a limit only speeds things up
for data transfer of the result set since MySQL still has to find all
the records, sort them, then deliver only the first X records. You can
usually tell how much time is spent on the transfer of the result set
vs. finding the
Thanks Brent and Donny, hopefully this info will help get to the root of the problem
with the fulltext search.
The table structure is very, very simple:
mysql> describe product_fulltext;
+-++--+-+-+---+
| Field | Type | Null | Key | Default | Extra
TECTED]
> Sent: Thursday, August 26, 2004 8:08 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Slow Queries on Fast Server?
>
> I'm gathering by the lack of response that perhaps MySQL is incapable of
> executing a count of the number of fulltext matches on 3 million rows.
> I really th
Capable? I can't think if why it wouldn't be capable. From your posts I
assume your definition of "capable" in this case is a quick response.
Are you running 4.0 or 4.1? I think the indexing was changed in 4.1 so
it would give you better response. 5-20 seconds does seem long,
assuming your
I'm gathering by the lack of response that perhaps MySQL is incapable of executing a
count of the number of fulltext matches on 3 million rows.
I really thought that MySQL 4 was really suppose to be able to handle such a load
I still think my configuration may be to blame
?
- John
>Could you send the output of an EXPLAIN for your query?
Sure, pretty sure the index is fine though:
mysql> EXPLAIN SELECT COUNT(*) FROM product_fulltext WHERE MATCH (search_text) AGAINST
('black');
+--+--+---+-+-+--+--+
Could you send the output of an EXPLAIN for your query?
--V
[EMAIL PROTECTED] wrote:
Have you checked the "Optimization" section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best performance. The query is c
>Have you checked the "Optimization" section of the manual yet?
>http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
Oh yes, as I've attempted to configure the my.cnf file for best performance. The
query is correct. The fulltext index is correct as I built the fulltext index on the
singl
Have you checked the "Optimization" section of the manual yet?
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
It's probably the best place to start.
Cheers,
--V
[EMAIL PROTECTED] wrote:
I'm running into a problem with some queries running on a dedicated mysql server (2.0
GHz, 2GB RAM).
Make sure when you are creating the database that
U_Number | int(9) unsigned | | PRI | NULL
is
U_Number | numeric() | | PRI | NULL
-Original Message-
From: Petre Agenbag [mailto:internet@;boesmanland.com]
Sent: Tuesday, November 12, 2002 4:27 AM
To: [E
To: "Bhavin Vyas" <[EMAIL PROTECTED]>
Sent: Friday, July 26, 2002 2:21 PM
Subject: Re: Slow queries
> At 05:53 PM 7/24/2002, you wrote:
> >Hello,
> >I am running mysql in our production environment, which have a few
reporting
> >tables with millions of row
Bhavin
I'm doing queries to one table with 4 other smaller tables.
The large table is 12-14 million records. With an index,
the table select is 13-15 seconds. With a compound index,
the delay is sub one second. Use explain to verify which
index is being used. Read the manual to tune your index's.
50 matches
Mail list logo
