It depends on the distribution of the 4 'values' in that field.
If the cardinality is poor, then INDEX(VAL) won't be used, and they will all do
a table scan.
> -Original Message-
> From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com]
> Sent: Friday, November 16, 2012 12:36 AM
> To: mys
SELECT * FROM XYX WHERE VAL IN ('BLUE','RED','GREEN'); query will make it
faster, if the field is ENUM
On Fri, Nov 16, 2012 at 12:36 AM, Anupam Karmarkar
wrote:
> Hi All,
>
> Consider a scenario, I have table XYZ which contains value follow
> BLUE
> RED
> GREEN
> NULL
>
> following are queries w
Your outer query "select cpe_mac,max(r3_dt) from rad_r3cap", is doing a full
table scan, you might want to check on this and use a "WHERE" condition to
use indexed column
On Fri, Sep 23, 2011 at 12:14 AM, supr_star wrote:
>
>
> I have a table with 24 million rows, I need to figure out how to op
Ah I see. Well thanks for your assistance!
-Brandon
On 09/08/2011 05:21 PM, Mihail Manolov wrote:
From the manual: "The default behavior for UNION is that duplicate rows are removed
from the result."
On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote:
Mihail,
Thanks so much! I modified yo
>From the manual: "The default behavior for UNION is that duplicate rows are
>removed from the result."
On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote:
> Mihail,
>
> Thanks so much! I modified your example to include the proper ORDER BY and
> LIMIT clauses and this, so far, is running super
Mihail,
Thanks so much! I modified your example to include the proper ORDER BY and
LIMIT clauses and this, so far, is running super fast (0.0007 seconds).
Question, if a record's open_dt is between the range AND the close_dt is
between the range as well, will the UNION output the record twic
Andrew,
Generally there is only 1 user performing the complicated SELECT query at a time, however
the background process that fills the table is constantly doing a fast SELECT (0.3
seconds) and a subsequent UPDATE. Basically whenever a connection is closed on the
firewall, the bg process
How about:
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
Partitioning isn't a bad idea for this however I'm still thinking about your
dataset size and possible hardware limitations. It's not likely going to fit
into relevant buffers/memory so you're going to be on disk more then you
want. You're probably creating temporary tables like crazy and I would b
Thanks for the idea Derek, however given the following query my EXPLAIN output
is identical:
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.ds
Mihail,
I have considered this but have not yet determined how best to go about
partitioning the table. I don't think partitioning by dst_address or
src_address would help because most of the queries do not filter on IP address
(except very specific queries where the end-user is searching the
Correct me if I'm wrong. You're wanting to get all records that have an
open_date or a close_date between two times.
If that's correct, you might be able to get an index_merge by doing a query
like:
WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting
time)<=close_dt<=(ending time))
Andy,
The queries take minutes to run. MySQL is 5.1.54 and it's running on Ubuntu
server 11.04. Unfortunately the machine only has 2GB of RAM but no other major
daemons are running on the machine. We are running RAID 1 (mirroring) with 1TB
drives. The tables in question here are all MyISAM
If you're running version 5.1+ you may wanna take a look at table partitioning
options you may have.
On Sep 8, 2011, at 2:27 PM, Brandon Phelps wrote:
> Thanks for the reply Andy. Unfortunately the users will be selecting varying
> date ranges and new data is constantly coming in, so I am not
I don't think I saw any query timings in the emails (maybe I missed them).
What version of MySQL are you currently using?
What does the explain look like when your remove the limit 10?
Is your server tuned for MyISAM or InnoDB?
What kind of disk setup is in use?
How much memory is in your machine?
Thanks for the reply Andy. Unfortunately the users will be selecting varying
date ranges and new data is constantly coming in, so I am not sure how I could
archive/cache the necessary data that would be any more efficient than simply
using the database directly.
On 09/08/2011 02:16 PM, Andre
Thinking outside the query, is there any archiving that could happen to make
your large tables kinder in the range scan?
Andy
On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps wrote:
> On 09/01/2011 01:32 PM, Brandon Phelps wrote:
>
>> On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
>>
>>> On 9
On 09/01/2011 01:32 PM, Brandon Phelps wrote:
On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
On 9/1/2011 09:42, Brandon Phelps wrote:
On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > ...
> > WHERE
> > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
00:00:00')
> > AND (open
On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
On 9/1/2011 09:42, Brandon Phelps wrote:
On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > ...
> > WHERE
> > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
00:00:00')
> > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '20
On 9/1/2011 09:42, Brandon Phelps wrote:
On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > ...
> > WHERE
> > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
00:00:00')
> > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '2011-08-30
12:36:53')
> In that case your logic
On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > SELECT
> >sc.open_dt,
> >sc.close_dt,
> >sc.protocol,
> >INET_NTOA( sc.src_address ) AS src_address,
> >sc.src_port,
> >INET_NTOA( sc.dst_address ) AS dst_address,
> >sc.dst_port,
> >
On Aug 30, 2011 6:46 PM, "Brandon Phelps" wrote:
> SELECT
>sc.open_dt,
>sc.close_dt,
>sc.protocol,
>INET_NTOA( sc.src_address ) AS src_address,
>sc.src_port,
>INET_NTOA( sc.dst_address ) AS dst_address,
>sc.dst_port,
>sc.sent,
>
There are a few things gonig on, but mainly it is the ORs that are killing
you.
As your require OR to examine two distinct columns, both of equal relevance
to the query, MySQL: is left with no choice but to do a full table scan on
what might be (at a guess) a very larger table. No amount of inde
> From: Brandon Phelps
>
> I am curious if there is any way I can better optimize the below query, as
> currently it takes this query around 10 seconds to run but I am sure this
> will get slower and slower as the database grows.
You need an index on `close_dt`.
> SELECT
> open_dt,
>
On 8/10/2011 1:01 PM, Brandon Phelps wrote:
Hello all,
I am using the query below and variations of it to query a database
with a TON of records. Currently the database has around 11 million
records but it grows every day and should cap out at around 150 million.
I am curious if there is an
Thanks Singer, this took my query down to 0.0007, perfect! I wasn't
aware a single index of multiple columns would work when one of the
columns was in the WHERE clause and the other in the ORDER BY clause.
Learn something new every day I guess!
On 08/10/2011 02:03 PM, Singer X.J. Wang wrote
Hi Gavin,Thanks very much, I'll implement as many of your suggestions as
possible. The varchar(255)'s are inexcusable and I feel suitably ashamed :)
The queries were generated by ActiveRecord (an ORM library for Ruby),
although even if I had written them myself they would probably not be much
bette
Hi Ciaran,
So I think there's a couple things going on:
1. The explain plan for your "slow" query looks wrong, such as mysql is
confused. It's possible your index statistics are incorrect. Try ANALYZE
TABLE on listings and addresses.
I think a sure way to fix it is to add STRAIGHT_JOIN to f
The index hint is not in productoin code.. I was trying ot force it to use
the index even when using the OR clause.. ment to take that out before I
sent the email.
The table structure is:
CREATE TABLE `customer` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ssn` varchar(32) DEFAULT NULL,
On Tue, Jan 13, 2009 at 7:07 PM, Baron Schwartz wrote:
>> If you have separate indexes on ssn and id_num, MySQL may be able to
>> efficiently use an index merge optimization . See
>> http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html.
>> This is only in 5.0+ - on older versions
> If you have separate indexes on ssn and id_num, MySQL may be able to
> efficiently use an index merge optimization . See
> http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html.
> This is only in 5.0+ - on older versions of MySQL you may find a union
> more efficient.
And in new
Do you have an index on id_num? What sort of explain output do you
get when you don't use a query hint? Your USE INDEX hint may be
causing MySQL to ignore a better strategy.
If you have separate indexes on ssn and id_num, MySQL may be able to
efficiently use an index merge optimization . See
ht
Try a union instead of an or condition.
http://dev.mysql.com/doc/refman/5.0/en/union.html
Johnny Withers wrote:
I have the following tables:
Customer: id,ssn
Customer_Id: id,customer_id,id_num
The customer table holds customers along with their SSN and the customer_id
table holds identificatio
First, you might want to move the WHERE...t3.int_a =
condition into the join condition for t3.
Your not using anything from t4, so I'm not sure why you have that
table in your query.
You can suggest or force mysql to use an index if it's using the wrong
one:
http://dev.mysql.com/doc/refma
-What I'am trying to do:
Bit hard to explain. I've got a table consisting of ip addresses
(ipv4_src), destination addresses (ipv4_dst), and port numbers
(port_dst) and some other irrelevant columns. Ultimately my goal is to
find a linear order in a subset of ports. For example, host A connects
to B
Joris Kinable schrieb:
Optimize query
I've got one query, which I would like to improve a lot since it takes
very long (>24 hours) to execute. Here is the idea:
1. Take the table (other rows in this
table are not mentioned for clearity) and remove all duplicate
tuple's. This is done by subquery
[EMAIL PROTECTED] wrote:
Yes, a ranged query should respond faster than a negation. In some cases
you can seriously improve query performance for a negation query if you
split it into two range queries unioned together. Here is a pseudo
example:
This query should be slow due to the table scan
Robert DiFalco wrote:
> In a previous database engine I was using an IN was more optimal than a
> <>. So, for example:
>
> SELECT * FROM table WHERE table.type IN (1,2,3);
>
> Where the possible values of type are 0-3, was appreciably faster than:
>
> SELECT * FROM table WHERE table.type <
ql.com
Subject: RE: Query Optimization Question
Yes, a ranged query should respond faster than a negation. In some cases
you can seriously improve query performance for a negation query if you
split it into two range queries unioned together. Here is a pseudo
example:
This query should be slow
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 13, 2006 6:37 AM
> To: Robert DiFalco
> Cc: mysql@lists.mysql.com
> Subject: Re: Query Optimization Question
>
>
>
>
> "Robert DiFalco" <[EMAIL PROTECTED]>
Shawn,
Any performance gains for specifying "type > 0" than "type <> 0" ?
R.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, March 13, 2006 6:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re
"Robert DiFalco" <[EMAIL PROTECTED]> wrote on 03/11/2006 12:43:43 PM:
> In a previous database engine I was using an IN was more optimal than a
> <>. So, for example:
>
> SELECT * FROM table WHERE table.type IN (1,2,3);
>
> Where the possible values of type are 0-3, was appreciably faster th
Robert DiFalco wrote:
In a previous database engine I was using an IN was more optimal than a
<>. So, for example:
SELECT * FROM table WHERE table.type IN (1,2,3);
Where the possible values of type are 0-3, was appreciably faster than:
SELECT * FROM table WHERE table.type <> 0;
I
Hi Tripp, all!
Tripp Bishop wrote:
[[...]]
Why would adding extra fields to the query slow the
query down so much? In either case it looks like it's
using the same index.
If a query is to return only fields contained in the index, it can be
executed as an "index-only" query, and the "true"
Jim, here's an example query:
SELECT COUNT(listing.listing_id) AS listing_count FROM
listing_city_xref INNER JOIN listing
ON listing.listing_id = listing_city_xref.listing_id
AND listing.group_id = listing_city_xref.group_id
WHERE listing_city_xref.group_id =2
AND listing_city_xref.city_id IN (8
It might be a good idea if you could post the results of an EXPLAIN SELECT ...
for a fast query and a slow query along with their actual SQL statements.
James Harvard
At 11:37 am -0800 12/1/06, Tripp Bishop wrote:
>I've got a question regarding optimizing a query.
--
MySQL General Mailing List
When you don't have subselects, you have two options: temporary tables or
JOINed queries.In your case, I think the temporary table is the better way
to go.
I would also eliminate the ABS() check so that I can compare values
directly against the index. I know the math is correct your way but th
Gerald Taylor wrote:
Query optimization question
I am selecting from a single table but it has a lot of rows and it
has a very involved calculation. What I really want to do is
is FIRST restrict the number of rows so that the big calculation is only
performed on the ones that are within 3 degr
I'm trying to sort out a query that identifies images that are not in the
story table (structures below). Peter Brawley has kindly pointed me in the
right direction with the sql structure (which does work), but I'm getting
benchmarks of 5+ seconds on the test data, whereas the live site has 50x
At 21:55 -0300 3/7/04, Volnei Galbino wrote:
Hi,
Does anybody know where I can find information about "query
optimization" in MySQL? Of which the techniques that are used?
Regards,
Volnei Galbino
Yes, there's a chapter on optimization in the MySQL Reference Manual.
http://www.mysql.com/doc/en/My
ge <<<<<<<<<<<<<<<<<<
On 2/25/04, 9:44:02 PM, <[EMAIL PROTECTED]> wrote regarding Re: Query
optimization help:
> Maybe i'm wrong here, someone correct me, if its just int's you are gonna
> use set the field types to bigint it
Chuck Gadd wrote:
I've got a query that I can't seem to get optimized, so I'm
hoping someone here can spot something I've missing!
Table has three columns:
CoordID int unsigned,
Zip_Lo char(9),
Zip_Hi char(9)
Table has 3 million records
indexes:
acg_lo (Zip_Lo)
acg_hi (Zip_Hi)
acg_combined (Zip_Lo
Maybe i'm wrong here, someone correct me, if its just int's you are gonna
use set the field types to bigint it may search faster you are doing a
character search, to get there quicker in a text search scenerio i'd
suggest mysql4 and full text searching MATCH AGAINST
> I've got a query that I can
Actually, I'm not convinced it would, because I want to be able to
find a string that is *NOT fully contained* within any given field.
In other words, I want a search for
The catcher in%
to match the row:
+-+--+
| article | tit
Mysql 4.0.1+ supports searches IN BOOLEAN MODE
So if mysql 4 is an option, I think that would work
for you.
http://www.mysql.com/doc/en/Fulltext_Search.html
olinux
--- Andy Ingham <[EMAIL PROTECTED]> wrote:
> Folks --
>
> We have built a table with bibliographic information
> that contains a
I'm sorry, I guess I should make sure the query syntax is correct when asking a
question... Here it is with the correct
field names:
SELECT main.*
FROM kw_table, kw_table AS kw_table1, main
WHERE kw_table.keyword LIKE 'first word%' AND kw_table1.keyword LIKE '2nd word%' AND
main.id = kw_table.
That is not valid SQL.
a table can't be like something.
I think you ommitted the field name.
I also don't see why you need a join , unless you have duplicate ids.
Gabe wrote:
>First off, please excuse me if this is the wrong list to post this sort of question
>to (and please direct me to the
>a
In the last episode (Jul 24), Dave Dutcher said:
> I was wondering if somebody could give me any suggestions on how to optimize
> a query I am working on.
>
> This is my table:
>
> CREATE TABLE testdata (
> Begin char(9) NOT NULL default '',
> End char(9) NOT NULL default '',
> UNIQUE KEY
Hi, I did some more research on my problem. (Sorry for not doing it before
I posted the other message), and I think perhaps my question could be
summarized into the following.
Can I use an index to speed up a select max() or select min()?
I read what the manual has to say about a query like thi
On 8 Jul 2002, at 16:07, Arul <[EMAIL PROTECTED]> wrote:
> FROM
> User_Type_Details UTD,
> User_Type_Details B , Users U ,TempGeneralSearch1 T1,Users
> A LEFT JOIN Company C on (U.CompanyID = C.CompanyID) LEFT
> JOIN Expert_Info EI on (U.UserID =
On Monday, 8. July 2002 12:37, Arul wrote:
> Hi All
>
> The Query below took around 175 Secs to return 22 Rows..Any way this query
> can be optimized
>
For analyzing your problem, you should send your table definition and an
output of the EXPLAIN statement.
Regards Georg
mysql, query
---
gt;
Cc: <[EMAIL PROTECTED]>
Sent: Monday, July 08, 2002 4:25 PM
Subject: Re: Query Optimization
> Pada Mon, 8 Jul 2002 16:07:04 +0530
> "Arul" <[EMAIL PROTECTED]> menulis :
>
> > Hi All
> >
> > The Query below took around 175 Secs to return 22
Pada Mon, 8 Jul 2002 16:07:04 +0530
"Arul" <[EMAIL PROTECTED]> menulis :
> Hi All
>
> The Query below took around 175 Secs to return 22 Rows..Any way this query
> can be optimized
try to use group by instead of distinct i usually found group by is faster ;-)
anyway ... performance of quer
Jeff,
Let's do some math here first. Your table has 47 Million rows. You
have a primary key on an int, and indexes across 22 chars and 10 chars.
You have 512MB of key buffer.
Now, assuming the indexes just have information on relevant fields
(they're actually bigger than this since they have
On Thu, Aug 23, 2001 at 11:11:38AM -0700, Steven Roussey wrote:
> >
> > mysql> explain select * from dominf.domip order by ip desc;
> >
> > 'ORDER BY something DESC' does not use an index. It must
> > be ascending. See the manual.
>
> Also, I forgot, if you do this often and it needs to be in DES
>
> mysql> explain select * from dominf.domip order by ip desc;
>
> 'ORDER BY something DESC' does not use an index. It must
> be ascending. See the manual.
Also, I forgot, if you do this often and it needs to be in DESC order, you
can always alter the app to create and use an extra column 'ip_de
mysql> explain select * from dominf.domip order by ip desc;
'ORDER BY something DESC' does not use an index. It must be ascending. See
the manual.
Sincerely,
Steven Roussey
http://Network54.com/?pp=e
-
Before posting, please
Rafal Jank wrote:
>
> Peter Zaitsev wrote:
> >
> > Hello mysql,
> >
> > I was trying to load very huge (5GB 45mil rows) to the text dump in
> > sorted order (according to primary key).
> > The problem is mysql somehow does not wants to scan the table by
> > primary key to produce sorted o
Peter Zaitsev wrote:
>
> Hello mysql,
>
> I was trying to load very huge (5GB 45mil rows) to the text dump in
> sorted order (according to primary key).
> The problem is mysql somehow does not wants to scan the table by
> primary key to produce sorted output row by row, but prefers to u
Hello
On Sat, 17 Mar 2001, Bård Farstad wrote:
> I have a query in my program which is taking to long when I get data
> in the database. I'm currently testing with ~40.000 articles in the
> system. [...] Is it possible to optimize this query further?
Have you played with EXPLAIN SELECT and fri
Hi,
Without a where clause in your initial query, you will never get to use an
index.
How many rows are there in sessobjs? From the start of the sample output, it
looks like there are several thousand, so asking a complex question may,
indeed, take a long time.
You could create a result table,
In the last episode (Feb 01), Michael Griffith said:
> CREATE TABLE test(
> userID int, # (non-unique)
> testID int, # (non-unique)
> PRIMARY key(testid,userid)
> );
>
> Suppose this table is populated with 1,000,000 rows. Then do this:
>
> DELETE FROM test WHERE userID= AND
> Shouldn't the first query have parenthesis? As in:
> DELETE FROM test WHERE userID=X AND (testID<20 OR
> testID>80);
> Even if it works the other way, parenthesis make it more clear what
> you're trying to accomplish.
> -Angela
Actually, yes. Sorry about the poor example. My point
Shouldn't the first query have parenthesis? As in:
DELETE FROM test WHERE userID=X AND (testID<20 OR
testID>80);
Even if it works the other way, parenthesis make it more clear what
you're trying to accomplish.
-Angela
Michael Griffith wrote:
>
> When using mySQL I've learned to avoid
74 matches
Mail list logo
