Simple query slow on large table
Hi Everyone, I'm having a very simple query often take several seconds to run and would be hugely grateful for any advice on how i might spped this up. The table contains around 500k rows and the structure is as follows: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | ID| int(11) | | PRI | NULL | auto_increment | | siteid| int(11) | | MUL | 0 | | | sender| varchar(255) | | | | | | subject | varchar(255) | | MUL | | | | message | text | | | | | | datestamp | timestamp| YES | MUL | CURRENT_TIMESTAMP | | | msgtype | int(1) | | MUL | 0 | | | isread| int(1) | | | 0 | | +---+--+--+-+---+--- -+ I have indexes on siteid, datestamp and msgtype. Queries such as the following are constantly appearing in the slow queries log: SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY datestamp DESC LIMIT 5; An EXPLAIN on the above query returns: ++-+---+--+++--- --+---+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+---+--+++--- --+---+--+-+ | 1 | SIMPLE | enquiries | ref | siteid,msgtype | siteid | 4 | const | 1940 | Using where; Using filesort | ++-+---+--+++--- --+---+--+-+ Shouldn't MySQL be using the datestamp index for sorting the records? When I remove the ORDER BY clause the query is considerably faster. Do I need to do something to make sure it using the index when sorting? Any help will be greatly appreciated! Regards Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple query slow on large table
Simon Kimber schrieb: Hi Everyone, I'm having a very simple query often take several seconds to run and would be hugely grateful for any advice on how i might spped this up. The table contains around 500k rows and the structure is as follows: +---+--+--+-+---+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+---+--- -+ | ID| int(11) | | PRI | NULL | auto_increment | | siteid| int(11) | | MUL | 0 | | | sender| varchar(255) | | | | | | subject | varchar(255) | | MUL | | | | message | text | | | | | | datestamp | timestamp| YES | MUL | CURRENT_TIMESTAMP | | | msgtype | int(1) | | MUL | 0 | | | isread| int(1) | | | 0 | | +---+--+--+-+---+--- -+ I have indexes on siteid, datestamp and msgtype. Queries such as the following are constantly appearing in the slow queries log: SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY datestamp DESC LIMIT 5; An EXPLAIN on the above query returns: ++-+---+--+++--- --+---+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+---+--+++--- --+---+--+-+ | 1 | SIMPLE | enquiries | ref | siteid,msgtype | siteid | 4 | const | 1940 | Using where; Using filesort | ++-+---+--+++--- --+---+--+-+ Shouldn't MySQL be using the datestamp index for sorting the records? When I remove the ORDER BY clause the query is considerably faster. Do I need to do something to make sure it using the index when sorting? Any help will be greatly appreciated! Regards hi Simon, you can try a join see http://www.artfulsoftware.com/infotree/queries.php for hints. sql is pretty bad for time series data. IMHO is the most obvious thing to reduce the number entries in your table. (do you realy need ID when you have a timestamp ?, etc) Otherwise the other stuff like: myisam instead of immodb but this depends on your requirements. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Simple query slow on large table
On Tue, Aug 18, 2009 at 5:08 AM, Simon Kimbersi...@internetstuff.ltd.uk wrote: I have indexes on siteid, datestamp and msgtype. Queries such as the following are constantly appearing in the slow queries log: SELECT * FROM enquiries WHERE siteid = 59255 AND msgtype = 0 ORDER BY datestamp DESC LIMIT 5; Read the explanation of ORDER BY optimization: http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html As it explains, you aren't providing a key it can use. If you create a multi-column index on siteid, msgtype, and datestamp, that will probably fix it. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fulltext index -first query slow, subsequent queries fast
Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley [EMAIL PROTECTED] wrote: mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 10:48 AM To: [EMAIL PROTECTED] Cc: mos; mysql@lists.mysql.com Subject: Re: Fulltext index -first query slow, subsequent queries fast Hi Is sphinxsearch avialable only on for windows regards anandkl On 6/13/08, Rory McKinley [EMAIL PROTECTED] wrote: mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ ---8--- snip GIYF: http://www.linux.com/feature/118721 I believe the *binaries* are only *pre-compiled* for Windows. Search the Sphinx site for info about compilation, or crack open one of the .tgz (an immediate I'm probably a *nix package flag) releases. I'll bet you can compile it yourself on *nix. (The article I've linked to also talks about using Sphinx in BSD distros.) Hope that helps, Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext index -first query slow, subsequent queries fast
Hi List
I have a table with a fulltext index across five fields, with about 2.2
million records and a data size of about 5.6 GB (index another 3.5 GB).
When I test a query that uses fulltext matching, the first run takes
about 15-16 seconds to complete. The second run takes about 0.1 sec and
subsequent runs will all all ge around the 0.1 sec time.
The query that I use has a call to NOW() as one of the criteria, so i
know that the query results will not be cached. Yet, the times would
suggest that some sort of caching effect is being observed.
Is there something I can do that can return more consistent query
performance - hopefully with a time somewhere between the two extremes?
The structure of the table as well as the query are below.
All help will be appreciated.
CREATE TABLE `article` (
`article_id` int(11) NOT NULL auto_increment,
`site_id` int(11) NOT NULL default '0',
`article_code` varchar(80) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`publication_id` int(11) default '0',
`rating_id` int(11) NOT NULL default '0',
`status_id` int(11) NOT NULL default '0',
`section_id` int(11) NOT NULL default '-1',
`template_id` int(11) default NULL,
`headline1` varchar(255) default NULL,
`headline2` varchar(255) default NULL,
`headline3` varchar(255) default NULL,
`live` enum('Y','N') NOT NULL default 'N',
`modified_date` datetime NOT NULL default '-00-00 00:00:00',
`original_date` datetime NOT NULL default '-00-00 00:00:00',
`flash` enum('Y','N') default NULL,
`expiry_date` datetime default NULL,
`embargo_date` datetime default NULL,
`embargo_hour` int(11) default NULL,
`embargo_day` int(11) default NULL,
`message` varchar(255) default NULL,
`section_front` enum('Y','N') NOT NULL default 'N',
`front_page` enum('Y','N') NOT NULL default 'N',
`author_id` int(11) default '0',
`is_urgent` enum('Y','N') NOT NULL default 'N',
`live_date` datetime default NULL,
`page_number` int(11) NOT NULL default '0',
`is_free` enum('Y','N') NOT NULL default 'N',
`source_id` int(11) default NULL,
`edition` int(11) default NULL,
`master_article` int(11) default NULL,
`newspapersection_id` int(11) default NULL,
`blurb` text NOT NULL,
`body` text NOT NULL,
`is_indexed` enum('Y','N') NOT NULL default 'N',
`zone` varchar(255) NOT NULL default '',
`warning` varchar(255) NOT NULL default '',
`blurb_is_intro` enum('Y','N') default 'N',
PRIMARY KEY (`article_id`),
KEY `site_id` (`site_id`),
KEY `article_code` (`article_code`),
KEY `name` (`name`),
KEY `publication_id` (`publication_id`),
KEY `rating_id` (`rating_id`),
KEY `status_id` (`status_id`),
KEY `section_id` (`section_id`),
KEY `live` (`live`),
KEY `modified_date` (`modified_date`),
KEY `original_date` (`original_date`),
KEY `expiry_date` (`expiry_date`),
KEY `section_front` (`section_front`),
KEY `front_page` (`front_page`),
KEY `live_date` (`live_date`),
KEY `is_urgent` (`is_urgent`),
KEY `page_number` (`page_number`),
KEY `author_id` (`author_id`),
KEY `embargo_date` (`embargo_date`),
KEY `master_article` (`master_article`),
KEY `newspapersection_id` (`newspapersection_id`),
KEY `site_status` (`site_id`,`status_id`),
KEY `flash` (`flash`),
FULLTEXT KEY `blurb`
(`blurb`,`body`,`headline1`,`headline2`,`headline3`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2586;
SELECT SQL_CALC_FOUND_ROWS article_id AS iArticleId, MATCH(blurb, body,
headline1, headline2, headline3) AGAINST (microsoft) AS dRelevance
FROM article WHERE embargo_date = NOW() AND status_id IN (-1, -6, -10)
AND site_id = 45 AND MATCH(blurb, body, headline1, headline2, headline3)
AGAINST (microsoft) ORDER BY embargo_date DESC LIMIT 0, 25
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
-Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 3:20 PM To: mysql Subject: Fulltext index -first query slow, subsequent queries fast Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. [JS] It may well be the file system that is doing the caching. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext index -first query slow, subsequent queries fast
From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2008 4:57 PM To: Jerry Schwartz; mysql Subject: Re: Fulltext index -first query slow, subsequent queries fast Jerry Schwartz wrote: File system, or disk caching, uses some kind of algorithm to hold chunks of files in system RAM. That way a program can get to it more quickly than if it had to go out to the disk. The algorithms vary, depending upon the smarts of the program and the smarts of the file system. The system might keep the most recently used stuff, the most frequently used stuff, even the stuff it thinks you will need based upon the pattern of use. Regards, snip Hi Jerry Thanks for the explanation. So, in short, I am most likely hitting a wall with the fulltext index, and I am just getting lucky cos of the disk caching, but I am not going to be able to get away from that initial slow load...rats. [JS] I can't agree or disagree. I used to be a performance consultant, but that was in another life and I don't know anything about MySQL's own caching algorithms. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
At 02:20 PM 6/12/2008, you wrote: Hi List I have a table with a fulltext index across five fields, with about 2.2 million records and a data size of about 5.6 GB (index another 3.5 GB). When I test a query that uses fulltext matching, the first run takes about 15-16 seconds to complete. The second run takes about 0.1 sec and subsequent runs will all all ge around the 0.1 sec time. The query that I use has a call to NOW() as one of the criteria, so i know that the query results will not be cached. Yet, the times would suggest that some sort of caching effect is being observed. Is there something I can do that can return more consistent query performance - hopefully with a time somewhere between the two extremes? Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext index -first query slow, subsequent queries fast
mos wrote: snip Why not switch to Sphinx full text search for MySQL? It is faster and can handle more data than MySQL's built in fulltext search. http://www.sphinxsearch.com/ Mike snip I have read about sphinx and the good performance boost it provides - unfortunately there is a lot of legacy code reading off the db, so I will need to get all sorts of stuff signed off, before I can make any major changes ;). But I will definitely look into it so that I can offer it as a possible solution. Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
Hello friends Id like to thanks all friends that helped with this question Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query slow
Dear friends is their any way to optimize this query bellow, it take +- 2minutes do complete, i think it becouse their no index by the emissao field SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC The table structure is bellow CREATE TABLE `sav00_sava0400_dbf` ( `unidade` double(2,0) default NULL, `duplicata` double(6,0) default NULL, `ordem` double(2,0) default NULL, `unidadeped` double(2,0) default NULL, `pedido` double(6,0) default NULL, `emissao` date default NULL, `vencto` date default NULL, `venctoorig` date default NULL, `cliente` double(5,0) default NULL, `cidade` double(4,0) default NULL, `estado` char(2) default NULL, `regiao` double(2,0) default NULL, `microregia` double(2,0) default NULL, `represent` double(3,0) default NULL, `comissao` double(5,2) default NULL, `valorface` double(15,2) default NULL, `valorbaixa` double(15,2) default NULL, `produtogrp` char(3) default NULL, `codbaixa` double(2,0) default NULL, `ocorrencia` double(2,0) default NULL, `databaixa` date default NULL, `jurosdev` double(15,2) default NULL, `jurospagos` double(15,2) default NULL, `dliquidupl` date default NULL, `jurospend` double(15,2) default NULL, `jurosmerc` double(15,2) default NULL, `saldodupl` double(15,2) default NULL, `jurosabona` double(15,2) default NULL, `statusfina` double(2,0) default NULL, `dstatusfin` date default NULL, `valorfatu` double(15,2) default NULL, `trans` date default NULL, `proc004` date default NULL, `agcobr` double(3,0) default NULL, `sitdupl` double(2,0) default NULL, `valordevol` double(15,2) default NULL, `valordesc` double(15,2) default NULL, `sr_recno` bigint(15) NOT NULL auto_increment, `sr_deleted` char(1) NOT NULL default '', `indkey_001` char(254) default NULL, UNIQUE KEY `sr_recno` (`sr_recno`), KEY `SAV00_SAVI0401_01` (`unidade`,`duplicata`,`ordem`,`sr_recno`), KEY `SAV00_SAVI0402_02` (`saldodupl`,`sr_recno`), KEY `SAV00_SAVI0403_03` (`proc004`,`sr_recno`), KEY `SAV00_SAVI0404_04` (`indkey_001`), KEY `SAV00_SAVI0405_05` (`represent`,`emissao`,`unidade`,`duplicata`,`ordem`,`sr_recno`), KEY `SAV00_SAVI0406_06` (`vencto`,`sr_recno`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
luiz Rafael wrote: Dear friends is their any way to optimize this query bellow, it take +- 2minutes do complete, i think it becouse their no index by the emissao field SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '2000-01-01' AND '2000-12-31' UNION ALL SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '1999-12-01' AND '1999-12-31' -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
Hi Jay Thanks for the help Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
Jay Pipes wrote: SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '2000-01-01' AND '2000-12-31' UNION ALL SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '1999-12-01' AND '1999-12-31' Why not: SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '1999-12-01' AND '2000-12-31' ?? ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
luiz Rafael wrote: SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC Are you sure this is what you really want? MONTH() is never greater than 12, so your query is equal to: SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) ORDER BY emissao In order to make your query work faster you should create an index on `emissao` and rewrite it using date ranges. So you query will become something like this (if you actually meant December'99 and whole year 2000): SELECT * FROM `sav00_sava0400_dbf` WHERE `emissao` BETWEEN '1999-12-01 00:00:00' AND '2000-12-31 23:59:59'; -- BR, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Similar simple query slow down dramatically, by just select one more field, why?
N,jjkj{ zwkozz
xjDear all,
I have asked the question days before, but no one seems interested in it
Considering table imgstore, defined as
create table a (
imgid int not null,
parent int,
imgtype char(3),
img longtext,
primary key (imgid),
key searchkey (parent, imgid)
) type = innodb;
contains about 1+ rows, about 800M in size;
SQL tested:
T1) select imgid, parent from a where parent = 10;
returns 3357 rows in 0.08 sec.
T2) select imgid, parent, imgtype from a where parent = 10;
return 3357 rows in 8.32 sec.!!!
T3) select imgid, parent from a where parent = 10 order by imgid;
returns 3357 rows in 0.1 sec
T4) select imgid, parent, imgtype from a where parent = 10 order by imgid;
returns 3357 rows in 25.88 sec!!!
T5) create table za select imgid, parent, imgtype from a;
10102 rows in 1.71 sec.
T6) select imgid, parent, imgtype from za where parent = 10 order by imgid;
3357 rows in 0.02 sec.
T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in
almost same speed.
Anybody can explain my questions:
Q1- What cause the slow down, T2 vs T1 and T4 vs T3?
Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance of
T6 and T4?
Best Reguards.
Re: Similar simple query slow down dramatically, by just select one more field, why?
Oscar Yen wrote: create table a ( imgid int not null, parent int, imgtype char(3), img longtext, primary key (imgid), key searchkey (parent, imgid) ) type = innodb; T1) select imgid, parent from a where parent = 10; returns 3357 rows in 0.08 sec. T2) select imgid, parent, imgtype from a where parent = 10; return 3357 rows in 8.32 sec.!!! T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in almost same speed. Anybody can explain my questions: Q1- What cause the slow down, T2 vs T1 and T4 vs T3? Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance of T6 and T4? Best Reguards. Q1: It's explained in the MySQL Documentation under Optimization - How MySQL Uses Indexes. See http://www.mysql.com/doc/en/MySQL_indexes.html and pay close attention to the 5th bullet. This explains the behavior you see from T7. Q2: I have no answer for you there, Sorry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Similar simple query slow down dramatically, by just select one more field, why?
N¬ë,j°jËkj{ zºÞw
«k©oz»¢z
¦ºxj×úèThanks for reply.
- Original Message -
From: Ludwig Pummer [EMAIL PROTECTED]
To: Oscar Yen [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, February 19, 2004 8:39 AM
Subject: Re: Similar simple query slow down dramatically, by just select one more
field, why?
*This message was transferred with a trial version of CommuniGate(tm) Pro*
Oscar Yen wrote:
create table a (
imgid int not null,
parent int,
imgtype char(3),
img longtext,
primary key (imgid),
key searchkey (parent, imgid)
) type = innodb;
T1) select imgid, parent from a where parent = 10;
returns 3357 rows in 0.08 sec.
T2) select imgid, parent, imgtype from a where parent = 10;
return 3357 rows in 8.32 sec.!!!
T7) After change the searchkey to (parent, imgid, imgtype), T1/T2, T3/T4 runs in
almost same speed.
Anybody can explain my questions:
Q1- What cause the slow down, T2 vs T1 and T4 vs T3?
Q2- Can I assume mysql pooly handled on large BLOB data, by comparing performance
of T6 and T4?
Best Reguards.
Q1:
It's explained in the MySQL Documentation under Optimization - How MySQL
Uses Indexes. See http://www.mysql.com/doc/en/MySQL_indexes.html and pay
close attention to the 5th bullet. This explains the behavior you see
from T7.
Yes, after alter index, the mysql will use index to speedup search.
But, what caused the mysql refuse to use index searchkey in question T2/T4,
even if you explicit specify the index you want by re-write the sql like following?
T2) select imgid, parent, imgtype from a force index(searchkey) where parent = 10;
It is clear that non-indexed column imgtype does not contained in where clause!!!,
Is there any method that I can PERSUADE mysql to use index searchkey?
Q2:
I have no answer for you there, Sorry.
That's OK, let's change the expression of poor handle:
Am I right to assume mysql will store BLOB data along with other columns, when ever
mysql need to fetch rows, it will read WHOLE row, including BLOB data from the disk,
even if the BLOB data will NOT be used during query?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query fast via mysql CLI, Same query slow via Perl DBI
Hi,
When executing this query through the 'mysql' command line utility, the
result is returned from the database server immediately and the database
server does not create a temporary file.
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = accounts.username AND
zone_records.type = 'username'
WHERE domains.host = 1)
UNION
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = domains.domain AND
zone_records.type = 'domain'
WHERE domains.host = 1)
ORDER BY domains.domain,zone_records.type
LIMIT 871;
When executing the exact same query in a Perl script via the DBI
interface, the database server takes minutes to return the results,
using large amounts of CPU whilst computing the results and creating a
temporary file in /var/tmp.
I have turned on query logging and verified that the query logged by the
server is the same when executed with both the mysql and perl clients so
it does not seem that the mysql command line is optimising the query in
any way.
I have restarted the database server between queries to ensure that the
results are not being cached.
The two clients are connecting from the same server using the same
username and password to login.
The structure of the tables concerned is as follows:
CREATE TABLE `accounts` (
`username` varchar(20) NOT NULL default '',
`owner` varchar(20) NOT NULL default '',
`type` varchar(10) default NULL,
`server` tinyint(3) default NULL,
`winserver` tinyint(3) default NULL,
PRIMARY KEY (`username`),
KEY `owner` (`owner`),
) TYPE=InnoDB
CREATE TABLE `domains` (
`domain` varchar(255) NOT NULL default '',
`username` varchar(20) NOT NULL default '',
`host` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`domain`),
KEY `username` (`username`)
) TYPE=InnoDB
CREATE TABLE `zone_records` (
`zone` varchar(255) NOT NULL default '',
`type` enum('username','domain','component','default') NOT NULL
default 'username',
`record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A',
`ttl` smallint(5) unsigned default NULL,
`data` varchar(255) NOT NULL default '',
KEY `zone` (`zone`),
KEY `zone_type` (`zone`,`type`)
) TYPE=InnoDB
Software versions:
mysql-4.0.12-standard-log
DBI-1.30
DBD-mysql-2.1018
I am really at a loss as to what could be causing this and what to do to
correct the problem. Any assistance or advice you can offer is greatly
appretiated.
Thanks in advance.
Dan Conlon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query fast via mysql CLI, Same query slow via Perl DBI
Daniel J. Conlon wrote:
Hi,
When executing this query through the 'mysql' command line utility, the
result is returned from the database server immediately and the database
server does not create a temporary file.
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = accounts.username AND
zone_records.type = 'username'
WHERE domains.host = 1)
UNION
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = domains.domain AND
zone_records.type = 'domain'
WHERE domains.host = 1)
ORDER BY domains.domain,zone_records.type
LIMIT 871;
When executing the exact same query in a Perl script via the DBI
interface, the database server takes minutes to return the results,
using large amounts of CPU whilst computing the results and creating a
temporary file in /var/tmp.
Perhaps perl is taking up so much memory that a temporary file is needed
to hold the results.
I have turned on query logging and verified that the query logged by the
server is the same when executed with both the mysql and perl clients so
it does not seem that the mysql command line is optimising the query in
any way.
I have restarted the database server between queries to ensure that the
results are not being cached.
The two clients are connecting from the same server using the same
username and password to login.
The structure of the tables concerned is as follows:
CREATE TABLE `accounts` (
`username` varchar(20) NOT NULL default '',
`owner` varchar(20) NOT NULL default '',
`type` varchar(10) default NULL,
`server` tinyint(3) default NULL,
`winserver` tinyint(3) default NULL,
PRIMARY KEY (`username`),
KEY `owner` (`owner`),
) TYPE=InnoDB
CREATE TABLE `domains` (
`domain` varchar(255) NOT NULL default '',
`username` varchar(20) NOT NULL default '',
`host` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`domain`),
KEY `username` (`username`)
) TYPE=InnoDB
CREATE TABLE `zone_records` (
`zone` varchar(255) NOT NULL default '',
`type` enum('username','domain','component','default') NOT NULL
default 'username',
`record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A',
`ttl` smallint(5) unsigned default NULL,
`data` varchar(255) NOT NULL default '',
KEY `zone` (`zone`),
KEY `zone_type` (`zone`,`type`)
) TYPE=InnoDB
Software versions:
mysql-4.0.12-standard-log
DBI-1.30
DBD-mysql-2.1018
I am really at a loss as to what could be causing this and what to do to
correct the problem. Any assistance or advice you can offer is greatly
appretiated.
Thanks in advance.
Dan Conlon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query slow on join and order by
Group, Here is my query: explain select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac My describe of the phones table and the ops table: Phones: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(11) | | MUL | NULL| auto_increment | | first_name | varchar(255) | YES | | NULL|| | last_name | varchar(255) | YES | | NULL|| | identifier | varchar(255) | YES | | NULL|| | prefix1| char(3) | YES | | NULL|| | suffix1| varchar(10) | YES | MUL | NULL|| | prefix2| char(3) | YES | | NULL|| | suffix2| varchar(4) | YES | | NULL|| | location | varchar(255) | YES | | NULL|| | mod_date | varchar(30) | YES | | NULL|| | vacant | char(1) | YES | | NULL|| | centrex| char(1) | YES | | NULL|| | voice_mail | varchar(255) | YES | | NULL|| | jack | varchar(10) | YES | | NULL|| | division | varchar(255) | YES | | NULL|| | unit | varchar(255) | YES | | NULL|| | hr_emp_no | varchar(10) | YES | | NULL|| | no_print | char(1) | YES | | NULL|| ++--+--+-+-++ Ops: ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | int(11) | | MUL | NULL| auto_increment | | phone | varchar(10) | YES | MUL | NULL|| | plid | varchar(25) | YES | | NULL|| | vacant | char(1) | YES | | NULL|| | box| varchar(30) | YES | | NULL|| | loc| varchar(30) | YES | | NULL|| | jack | varchar(30) | YES | | NULL|| | mac| varchar(20) | YES | | NULL|| | phone_type | varchar(30) | YES | | NULL|| ++-+--+-+-++ And here is my explain of the query: table typepossible_keys key key_len ref rowsExtra ops ALL phone NULLNULLNULL345 where used; Using temporary; Using filesort phones ALL suffix1 NULLNULLNULL809 where used I am very sure that my bottle-neck has to do with the creation of a temporary file for sorting. What can I do to optimize this. Thanks to all. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query slow (again)
Hi, I have changed my query but it is so slow :( Please, help me! Thank u very much! Edilson. - Query - SELECT a.idemail, a.fklastresp FROM wmkt_email a, wmkt_client b, wmkt_maillist_client c LEFT JOIN wmkt_email_sent d ON a.idemail=d.fkemail WHERE d.fkemail IS NULL AND c.bActive AND c.fkmaillist IN (2) AND a.idemail=b.fkemail AND c.fkclient=b.idclient ORDER BY fklastresp LIMIT 1000 - Tables structures - Table,Create Table wmkt_email,CREATE TABLE `wmkt_email` ( `idemail` int(11) NOT NULL auto_increment, `email` varchar(255) NOT NULL default '', `fklastresp` int(11) NOT NULL default '0', PRIMARY KEY (`idemail`), UNIQUE KEY `ixEmail` (`email`) ) TYPE=MyISAM Table,Create Table wmkt_client,CREATE TABLE `wmkt_client` ( `idclient` int(11) NOT NULL auto_increment, `realname` varchar(80) NOT NULL default '', `fkemail` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `dtLastUp` datetime default NULL, `fkuser` int(11) NOT NULL default '1', PRIMARY KEY (`idclient`), KEY `ixFkemail` (`fkemail`) ) TYPE=MyISAM - Table,Create Table wmkt_maillist_client,CREATE TABLE `wmkt_maillist_client` ( `fkmaillist` int(11) NOT NULL default '0', `fkclient` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `bActive` tinyint(4) NOT NULL default '1', KEY `ixEmailList` (`fkmaillist`,`fkclient`) ) TYPE=MyISAM - Table,Create Table wmkt_email_sent,CREATE TABLE `wmkt_email_sent` ( `idemailsent` int(11) NOT NULL auto_increment, `fkpbl` int(11) NOT NULL default '0', `fkemail` int(11) NOT NULL default '0', `dtSend` datetime default NULL, `nResult` int(11) NOT NULL default '0', `dtLastUp` datetime default NULL, `nMachine` int(11) NOT NULL default '0', PRIMARY KEY (`idemailsent`), UNIQUE KEY `ixUEmailPbl` (`fkemail`,`fkpbl`), KEY `ixnMacPbl` (`nMachine`,`fkpbl`,`fkemail`) ) TYPE=MyISAM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query slow (again)
Hi, Try this... Do an alter and create indexes on the following fields of your tables: -wmkt_email_sent.fkemail -wmkt_client.fkemail -wmkt_maillist_client.fkclient ... and i think it's a good idea if you create indexes on the primary keys of each table.It's redundant but have some effect. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] To: MYSQL [EMAIL PROTECTED] Sent: Tuesday, May 14, 2002 8:31 PM Subject: Query slow (again) Hi, I have changed my query but it is so slow :( Please, help me! Thank u very much! Edilson. - Query - SELECT a.idemail, a.fklastresp FROM wmkt_email a, wmkt_client b, wmkt_maillist_client c LEFT JOIN wmkt_email_sent d ON a.idemail=d.fkemail WHERE d.fkemail IS NULL AND c.bActive AND c.fkmaillist IN (2) AND a.idemail=b.fkemail AND c.fkclient=b.idclient ORDER BY fklastresp LIMIT 1000 - Tables structures - Table,Create Table wmkt_email,CREATE TABLE `wmkt_email` ( `idemail` int(11) NOT NULL auto_increment, `email` varchar(255) NOT NULL default '', `fklastresp` int(11) NOT NULL default '0', PRIMARY KEY (`idemail`), UNIQUE KEY `ixEmail` (`email`) ) TYPE=MyISAM Table,Create Table wmkt_client,CREATE TABLE `wmkt_client` ( `idclient` int(11) NOT NULL auto_increment, `realname` varchar(80) NOT NULL default '', `fkemail` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `dtLastUp` datetime default NULL, `fkuser` int(11) NOT NULL default '1', PRIMARY KEY (`idclient`), KEY `ixFkemail` (`fkemail`) ) TYPE=MyISAM - Table,Create Table wmkt_maillist_client,CREATE TABLE `wmkt_maillist_client` ( `fkmaillist` int(11) NOT NULL default '0', `fkclient` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `bActive` tinyint(4) NOT NULL default '1', KEY `ixEmailList` (`fkmaillist`,`fkclient`) ) TYPE=MyISAM - Table,Create Table wmkt_email_sent,CREATE TABLE `wmkt_email_sent` ( `idemailsent` int(11) NOT NULL auto_increment, `fkpbl` int(11) NOT NULL default '0', `fkemail` int(11) NOT NULL default '0', `dtSend` datetime default NULL, `nResult` int(11) NOT NULL default '0', `dtLastUp` datetime default NULL, `nMachine` int(11) NOT NULL default '0', PRIMARY KEY (`idemailsent`), UNIQUE KEY `ixUEmailPbl` (`fkemail`,`fkpbl`), KEY `ixnMacPbl` (`nMachine`,`fkpbl`,`fkemail`) ) TYPE=MyISAM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
