On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:
Ok. Just found I gave wrong info. To make my life easier, the person who
created db named cust_id in 'orders' table as SoldTo
[EMAIL PROTECTED]
in this case,
select cust_id from customers
where cust_id not in
(select Soldto from orders);
will
Curtis [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, September 25, 2006 11:47 PM
Subject: Help with query
I'm trying to do a keyword search within a phrase saved in a table.
Here's the query:
SELECT * from closedtickets WHERE
keyphrase LIKE '%$keyword1%'
OR keyphrase LIKE
Hi, all
The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation.
There are 2 tables something like these:
Table A:
--
location|timestamp | other fields
---
Table B
location|timestamp |
: 860.674.8341
-Original Message-
From: Xiaobo Chen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 26, 2006 10:09 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Help for query
I found if I divided into 2 steps, I will find the record in table B:
Ta - the given
I found if I divided into 2 steps, I will find the record in table B:
Ta - the given timestamp from table A;
1) select min(abs(Ta - timestamp)) as min_t from B;
2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t =
Ta);
But, how can I make these 2 steps into 1 query?
.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Xiaobo Chen [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 26, 2006 10:09 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Help for query
I found if I divided into 2 steps, I will find the record
I'm trying to do a keyword search within a phrase saved in a table.
Here's the query:
SELECT * from closedtickets WHERE
keyphrase LIKE '%$keyword1%'
OR keyphrase LIKE '%$keyword2%'
OR keyphrase LIKE '%$keyword3%'
The problem I'm having is that the query is returning every record in
Curtis [mailto:[EMAIL PROTECTED]
Sent: Monday, September 25, 2006 2:18 PM
To: mysql@lists.mysql.com
Subject: Help with query
I'm trying to do a keyword search within a phrase saved in a table.
Here's the query:
SELECT * from closedtickets WHERE
keyphrase LIKE '%$keyword1%'
OR keyphrase LIKE
I have the following query which works in MS SQL Server, but I'm sure the
syntax is different for Myself. I'm sure it's in the area of the LIKE
clause. I don't know how to do this with Myself. Can anyone point me in
the right direction?:
SELECT LA.FirstName,LA.LastName,LA.EMailAddress,
I think you are trying to use a regular expression in the like
phrase. I wasn't aware that MS SQL can do regular expressions at
all? I've been using 2000 - perhaps the newer version can?
In MySQL, instead of saying:
LA.LastName LIKE '[A-E]%'
try this:
LA.LastName REGEXP '^[A-E]'
You can
appreciate your
help.
Jesse
- Original Message -
From: Douglas Sims
To: Jesse
Cc: MySQL List
Sent: Monday, August 21, 2006 12:07 PM
Subject: Re: Help with query
I think you are trying to use a regular expression in the like phrase. I
wasn't aware that MS SQL can do regular
Thanks Peter,
That looks pretty good to me. I never would have figured that out on
my own.
Dan T
On Jun 1, 2006, at 4:06 PM, Peter Brawley wrote:
Dan,
I want to get a particular users 'rank' or row number from the query.
SELECT 1+COUNT(*) AS Rank
FROM results r1
INNER JOIN results r2
I have a regular query lets say:
SELECT user, points FROM results ORDER BY points DESC
so I get:
userpoints
---
john23
steve 17
bill14
From this I want to get a particular users 'rank' or row number from
the query. I was hoping for a single line elegant query for
Dan wrote:
I have a regular query lets say:
Better to show the real query, rather than a simplified version. Simplified
requests get you simplified answers. What seems like a simplification to you,
may in fact hide a crucial piece of information, thus preventing anyone from
giving you a
Dan,
I want to get a particular users 'rank' or row number from the query.
SELECT 1+COUNT(*) AS Rank
FROM results r1
INNER JOIN results r2 ON r1.pointsr2.points
WHERE r1.user='Steve';
PB
-
Dan wrote:
I have a regular query lets say:
SELECT user, points FROM results ORDER BY points DESC
Hi All,
We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid =
Kishore,
We use Vbulletin for our message boards, and I have a
query which
takes approximately 1.2 - 1.5 seconds to execute, on a table
('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread
LEFT JOIN deletionlog
ON ( thread.threadid = deletionlog.primaryid AND
3 tables:
table1 - table2 (one to many)
table2 - table3 (one to one) [designed like this]
how to achieve this?
select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4)
from table1, table2, table3 where table1.field1 = table2.field1 and
table2.field1 = table3.field1
regards
how to achieve this?
select table1.*, table2.*, table3.*, sum(table2.field3), sum(table2.field4)
from table1, table2, table3 where table1.field1 = table2.field1 and
table2.field1 = table3.field1
Your question as formulated has no answer. If you query aggregate
values like Sum on a table, you
Hi:
I'm develop a simple shopping cart. I have this two tables:
carro_de_compras
--
IDU int(11) NOT NULL
IDA int(11) NOT NULL
CantidadDeArticulos int(11) NOT NULL
os_articulo
--
IDA int(11) NOT NULL auto_increment,
IDC int(11) NOT NULL default '0',
ANombre varchar(200) NOT NULL
Dear Reynier,
You can use JOIN on your both,
The JOIN have to run on the same feilds i.e IDA.
SELECT * FROM carro_de_compras LEFT JOIN os_articulo ON carro_de_compras.IDA
= os_articulo.IDA
This query returns all your users with their articles if any and you can
iterate on it.
but one note:
Use
Hello!
I am building a query to get a monthly total for receipts and receipts plus
TAX.
My problem is the TAX, it can be different for each receipt.
I need help on including and relating each TAX value/id with each receipt,
like receipt_items.price * 1.21 etc.
The tables are like the following
Solved.
Thanks.
-Mensagem original-
De: pedro mpa [mailto:[EMAIL PROTECTED]
Enviada: domingo, 29 de Janeiro de 2006 18:25
Para: mysql@lists.mysql.com
Assunto: Help on query joining a 3rd table
Hello!
I am building a query to get a monthly total for receipts and receipts plus
TAX.
My
From the result set below I have 22 rows and the only difference is the
date. I was wondering if there was a way to get all of these results using
GROUP BY instead of having to use LIMIT??
As this table grows I'm going to want to get a LIMIT 0,77 but would like it
to be grouped by date. So
Jay Paulson [EMAIL PROTECTED] wrote on 01/25/2006 10:09:36 AM:
From the result set below I have 22 rows and the only difference is the
date. I was wondering if there was a way to get all of these results
using
GROUP BY instead of having to use LIMIT??
As this table grows I'm going to want
I have a table of votes with four fields, a primary key, and userID, that are
just there for tracking purposes. But then I have questionID and vote fields.
For each question, a user could pick a number of it's importance from 1 to 5.
That number is what gets stored in the vote field.
So,
[EMAIL PROTECTED] wrote on 10/22/2005 06:24:07 PM:
I have a table of votes with four fields, a primary key, and userID,
that are just there for tracking purposes. But then I have
questionID and vote fields. For each question, a user could pick a
number of it's importance from 1 to 5.
ja,
Your question is a little cryptic. If a questionid column value
identifies a particular question, and a vote column value contains a
user's vote (1,2,3, c), can't you just write...
SELECT questionid, vote,count(vote)
FROM ...
GROUP BY questionid, vote
PB
-
[EMAIL PROTECTED] wrote:
Shawn,
- Original Message -
From: [EMAIL PROTECTED]
To: Dušan Pavlica
Cc: Michael Stassen ; list mysql
Sent: Wednesday, October 12, 2005 4:45 PM
Subject: Re: Help with query
Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM:
Thanks, Michal, for your
Hello,
could someone help me please to construct correct query or tell me what I'm
doing wrong?
I have three tables:
table products
table products_codes where some products could have assigned another additional
codes
table products_prices
I want to get all rows from product_prices listed
Dušan Pavlica wrote:
Hello,
could someone help me please to construct correct query or tell me what I'm
doing wrong?
I have three tables:
table products
table products_codes where some products could have assigned another additional codes
table products_prices
I want to get all rows from
Thanks, Michal, for your help.
Your query works as I need.
I tried to use same syntax as in MS Access, but results are for some reason
different for this query. I'm working on application which should be able to
connect to MySQL or to MSAccess (users' choice) and I didn't want to write
Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM:
Thanks, Michal, for your help.
Your query works as I need.
I tried to use same syntax as in MS Access, but results are for some
reason
different for this query. I'm working on application which should be
able to
connect
On 04/10/2005, Jasper Bryant-Greene wrote:
Kishore Jalleda wrote:
Could you kindly advice if this query can be made to run faster
SELECT title, template
FROM template
WHERE templateid IN
(608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,189
Hi All,
Could you kindly advice if this query can be made to run faster
SELECT title, template
FROM template
WHERE templateid IN
Kishore Jalleda wrote:
Could you kindly advice if this query can be made to run faster
SELECT title, template
FROM template
WHERE templateid IN
Hello,
I have a table where is saved all site´s access:
access
id
year
month
day
weekday
hour
minute
ip
Any column has multiple lines, and I have the follow query
that returns the amount of access per day of month:
SELECT year, month, day, COUNT(*) AS access
FROM access
WHERE year = 2005
Hi,
look at group by ... with rollup at :
http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html
Mathias
Selon Ronan Lucio [EMAIL PROTECTED]:
Hello,
I have a table where is saved all site´s access:
access
id
year
month
day
weekday
hour
minute
ip
Any column has multiple
Mathias,
Hi,
look at group by ... with rollup at :
http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html
Thank you very much for your help.
My needs aren´t this, exactly.
GROUP BY WITH ROLLUP, returns me several lines of the
same day (one per IP), plus the total.
I need that every
Ronan Lucio [EMAIL PROTECTED] wrote on 05/16/2005 04:21:17 PM:
Mathias,
Hi,
look at group by ... with rollup at :
http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html
Thank you very much for your help.
My needs aren´t this, exactly.
GROUP BY WITH ROLLUP, returns me several
Hi Ronan,
I don't know if i understand your need, but your query gives something like that
:
mysql SELECT year, month, day, ip, COUNT(*) AS access
- FROM access
- WHERE year = 2005
- AND month = 5
- GROUP BY year, month, day, ip
- ORDER BY year, month, day;
Mathias,
To drop multiple IP, you can use distinct :
mysql SELECT year, month, day, group_concat(distinct ip),count(*) AS
access
- FROM access
- WHERE year = 2005
- AND month = 5
- GROUP BY year, month,day
- ORDER BY year, month, day;
I wrong?
Anyway, thank you for all your advices, I'm really learning a lot of things
with that case!
Melanie
From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 12:43:06 -0500
mel list_php [EMAIL
with that case!
Melanie
From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 12:43:06 -0500
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30
AM:
Hi again,
Thanks
for your help, I will give temporary tables
an other chance!!!
Melanie
From: [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Wed, 16 Mar 2005 13:52:44 -0500
I have a favorite technique for improving the results of queries
Responses embedded below
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM:
Hi Shawn,
Thank you very much, I'm impressed by the time you took to answer me,
and
the quality of the reply!!!
I forwarded the answer to my friend.
I'm wondering, I knew the mechanism of temporary
[EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: Re: help on query/group by
Date: Fri, 18 Mar 2005 09:14:02 -0500
Responses embedded below
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 05:57:29 AM:
Hi Shawn,
Thank you very much, I'm impressed by the time you took to answer me
mel list_php [EMAIL PROTECTED] wrote on 03/18/2005 10:35:30 AM:
Hi again,
Thanks for the explanation about the join and the group by.
I wanted to test your query (almost a simple copy/paste :-)) ).
The first 2 queries are ok, but the third one still is too long :
mysql CREATE
Hi,
A friend of mine asked me to have a look at one of his query, and I'm
stuck
Here was his query:
SELECT drugID, protID, COUNT(DISTINCT pmid),
MAX(s1.syn) AS o1, MAX(s2.syn) AS o2
FROM matches
INNER JOIN synonyms AS s1 ON drugID=s1.nameID AND s1.syn LIKE 'a%'
INNER JOIN synonyms AS s2
I have a favorite technique for improving the results of queries , like
this, which involve fairly large JOINed tables. It's a form of
divide-and-conquer in that you pre-compute what you can then make the
JOINS you need to finish up the results.
I agree that the AND s2.syn LIKE '%' in the ON
Hello,
I have two tables:
Table2 (listings), columns - listingID, state, preferred, siteAddress,
siteTitle, siteDescription
Table1 (invotes), columns listingID
I want to select the listingID of the rows in Table2 where the 'state'
column equals '$st' then count the number of rows in
Could someone please offer a little help.
I have a table like:
Year, Month, Start_date
20041020041102
20041120041203
20041220050104
20050120050204
20050220050303
I need to get the latest Year,Month for a given date, so for example today
(20050204) I
Graham Cossey wrote:
Could someone please offer a little help.
I have a table like:
Year, Month, Start_date
20041020041102
20041120041203
20041220050104
20050120050204
20050220050303
I need to get the latest Year,Month for a given date, so for example today
snip
As I'm using 4.0.20 I can't use subqueries so how can I create
a query that
does this?
SELECT year, month
FROM `dc_months`
WHERE start_date = (SELECT MAX(start_date)
from dc_months
where start_date = '20050204')
Any help
On Fri, 2005-02-04 at 09:19, Graham Cossey wrote:
Could someone please offer a little help.
I have a table like:
Year, Month, Start_date
20041020041102
20041120041203
20041220050104
20050120050204
20050220050303
I need to get the latest
I have what I thought was a simple, well-indexed query, but it
turns out that it's acting as a pretty big drag. The one thing
that's clearly a problem (though I'm not sure of the extent of
the problem), I'm not sure how to fix.
There are three tables: citations, subjects, and a many-to-many
* Jesse Sheidlower
[...]
CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `citation_id` (`citation_id`,`subject_id`)
)
Try adding an index with
On Tue, Nov 23, 2004 at 09:55:15PM +0100, [EMAIL PROTECTED] wrote:
* Jesse Sheidlower
[...]
CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
Your original query (implicit INNER JOINs):
SELECT teu.name, eca.owner_id, ece.value
FROM typed_enterprise_unit teu,
e_contact_association eca,
e_contact_entry ece
WHERE teu.unit_id=eca.owner_id
and eca.entry_id=ece.entry_id
and eca.type_id=68
and
Dear list,
i have some small tables but for some reason the mysql took very long to
find the results. my query looks
like below and mysql'e explain is attached for better format. Thanks for
your help!
select teu.name, eca.owner_id, ece.value
fromtyped_enterprise_unit teu,
Thanks for the advice Steven, I'll bear it in mind and do some reading.
Graham
-Original Message-
From: Steven Roussey [mailto:[EMAIL PROTECTED]
Sent: 13 November 2004 02:52
To: 'Graham Cossey'
Cc: [EMAIL PROTECTED]
Subject: RE: Help with query performance anomaly
For production
It turns out that it appears to be a data discrepancy that caused the query
optimiser to, well, not optimise.
I thought the main table (r) with 3million records would be the problem, but
it was table p with 3100 records on the live server and 3082 records on my
dev pc that caused the problem.
For production systems, I would never let the mysql optimizer guess a query
plan when there are joins of big tables and you know exactly how it should
behave. Once you think a query is finished, you should optimize it yourself.
Use STRAIGHT_JOIN and USE INDEX as found here in the manual:
Hi
Can someone offer any advice on a strange problem I have at present...
If I run a certain query (see below) on my local development PC using
mysqlcc it returns in 3.7s.
If I run the exact same query on my live webserver (again using mysqlcc) I
have yet to get a result !!
Both databases have
What does EXPLAIN show for the query on both systems? (I am wondering if
you may have an index on your development system that you do not have on
your production server.)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004
like this? Maybe something in the configs?
Thanks
Graham
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 16:28
To: Graham Cossey
Cc: [EMAIL PROTECTED]
Subject: Re: Help with query performance anomaly
What does EXPLAIN show for the query
PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 16:28
To: Graham Cossey
Cc: [EMAIL PROTECTED]
Subject: Re: Help with query performance anomaly
What does EXPLAIN show for the query on both systems? (I am wondering
if
you may have an index on your development system
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in key definitions?
Can 2 installations with the same table definitions produce different
Response at end
Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 12:19:17 PM:
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in
[big snip]
These are two different plans. Your development machine is using
the index
yr_mn_pc on the r table and is joining that table last. On your
production
server, the r table is joined second and is joined by the index PRIMARY.
Let me know how the ANALYZE TABLE I suggested in a
How do the OS statistics look on both boxes. Do top, sar, vmstat or
iostat show any CPU, memory or I/O performance issues? Does anything
odd appear in the /var/log/messages file?
-Jamie
On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey
[EMAIL PROTECTED] wrote:
[big snip]
These are
!!
Comments?
Advice?
Thanks
Graham
-Original Message-
From: Jamie Kinney [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 19:25
To: Graham Cossey
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Help with query performance anomaly
How do the OS statistics look on both boxes. Do
[snip]
Have just run 'top' on the live server...
Before running the query I get:
13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44
24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0%
idle
Hi,
I have two tables:
TABLE_1
===
- id
- name
TABLE_2
===
- id
- table1_id
- name
How could I make a select on table_1 that returns me only the
rows that don´t have any reference in table_2?
Any help would be appreciated.
Thank´s,
Ronan
--
MySQL General Mailing List
For list
SELECT `TABLE_1`.* FROM `TABLE_1` JOIN `TABLE_2` USING (`id`) WHERE
`TABLE_2`.`id` IS NULL
Asuming that the reference is the id field...
Regards, Jigal.
- Original Message -
From: Ronan Lucio [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, October 27, 2004 3:12 PM
Subject: Help
This is a very FAQ:
SELECT t1.*
FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2
ON t1.id = t2.table1_id
WHERE t2.id is null
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Ronan Lucio [EMAIL PROTECTED] wrote on 10/27/2004 10:12:42 AM:
Hi,
I have two tables:
TABLE_1
Shawn,
Thank you very for your answer.
Actually, I thought that a main SELECT couldn´t be filtered
by the WHERE clause refered to a field in a LEFT JOIN.
Now, looking better in the JOIN documentation I see this
issue.
Thank´s,
Ronan
This is a very FAQ:
SELECT t1.*
FROM TABLE_1 t1
I'm having a difficult time getting a query to work
correctly.
I'm not sure, if this is proprietary to Dreamweaver or
not (that is what I do my application development in)
Anyway I have created a search page that is dynamic
(meaning up to the user to choose the criteria)
It all works fine
I am trying to come up with a query that shows duplicate last names and the
order numbers for each occurance of.
I can get as far as determining the duplicates but my query result only
outputs one order for each.
Here is my current query, an example of the results and an example of the
results I
Please post the structure of your orders table. (SHOW CREATE TABLE
orders). I need to know what you are using as a primary key in order to
help you to uniquely identify each duplicated row.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Paul Fine [EMAIL PROTECTED] wrote
If you are using 4.1, you can look into the GROUP_CONCAT function.
Otherwise, try a self join like this:
SELECT A.LastName,A.OrderNum,B.OrderNum
FROM Orders AS A
LEFT JOIN Orders AS B ON A.LastName=B.LastName
WHERE A.OrderNum!=B.OrderNum
ORDER BY A.LastName
That joins the Order table with itself
Brent Baisley wrote:
If you are using 4.1, you can look into the GROUP_CONCAT function.
Otherwise, try a self join like this:
SELECT A.LastName,A.OrderNum,B.OrderNum
FROM Orders AS A
LEFT JOIN Orders AS B ON A.LastName=B.LastName
WHERE A.OrderNum!=B.OrderNum
ORDER BY A.LastName
That joins the
Hello,
A have two tables:
City:
CityID
CityName
Client:
ClientID
ClienteName
CityID
How can I make a SELECT that returns me only the cities
that have more than 30 (example) clients?
Thanks,
Ronan
--
MySQL General Mailing List
For list archives:
SELECT CityName, Count(ClientID) as ClientCount
FROM City
INNER JOIN Client
on City.CityID = Client.CityID
GROUP BY CityName
HAVING ClientCount 30;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Ronan Lucio [EMAIL PROTECTED] wrote on 09/10/2004 11:14:37 AM:
Shawn
SELECT CityName, Count(ClientID) as ClientCount
FROM City
INNER JOIN Client
on City.CityID = Client.CityID
GROUP BY CityName
HAVING ClientCount 30;
Thank you very much,
It should solve by problem... :-)
Ronan
--
MySQL General Mailing List
For list archives:
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] writes:
Have you considered splitting this into two queries? One query can gather
information from your token and token_ins tables. The other would join the
first queries results to the other tables to complete your original query.
Depending
Hello everyone,
I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think that's normal.
Here's the output of EXPLAIN:
mysql
On 15 Jul 2004 at 6:27, Patrick Drouin [EMAIL PROTECTED]
wrote:
I'm having a hard time with the following query. It
retrieves about 3K rows from a few tables. One of them
contains over 40M rows. When run on a 3Ghz server with
1G of RAM it returns the rows in more than 1 mini. I
don't think
Bonjour Arnaud,
--- Arnaud [EMAIL PROTECTED] wrote:
On 15 Jul 2004 at 6:27, Patrick Drouin
Your indexes look good, but I see that you have some
varchar fields.
Maybe
you could run an optimize table on these tables?
I'm running it at the moment, I will follow-up on the
list when it's done.
What version of MySQL are you using? Have you checked the cardinality on
these tables?
-Original Message-
From: Patrick Drouin
To: [EMAIL PROTECTED]
Sent: 7/15/04 8:27 AM
Subject: Need help optimizing query
Hello everyone,
I'm having a hard time with the following query. It
retrieves
Hello Victor,
What version of MySQL are you using? Have you
checked the cardinality on
these tables?
Problem solved! Optimizing the table brought the query
time down to 17 secs Wow!
Thanks for the input Victor and merci to Arnaud for
the quick fix.
Patrick
Hum,
Well, I'm back with another one... When adding a
join to the previous query, it sloows down once again
even though it retrieves less datat. Here's the info :
mysql explain SELECT ti.posi, ti.docid, d.filename,
ti.id, c.name
FROM corpus_documents cd, corpus c, documents d,
tokens_ins ti,
Have you considered splitting this into two queries? One query can gather
information from your token and token_ins tables. The other would join the
first queries results to the other tables to complete your original query.
Depending on your data, one of these subqueries should return a smaller
Hi everyone,
I am having a rough time with a query, which seems to be taking so long
it hangs the systems.
SELECT
l.CatalogNumber,
l.PDFLink,
l.PDFName,
l.Title,
p.PublisherName,
c.ComposerLname,
a.ArrangerLname,
l.Price,
l.Description,
o.Alias
A and B will return 200 rows (every row of
A will be joined with every row of B!).
-Original Message-
From: Erich Beyrent [mailto:[EMAIL PROTECTED]
Sent: Fri 2/6/2004 8:46 AM
To: [EMAIL PROTECTED]
Cc:
Subject: Help with query
, February 06, 2004 8:53 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Help with query
You will need parentheses around the 'or' clauses of your where clause.
You also don't seem to join the categories table with any other tables. If
you don't join tables you will create what
]
Sent: Fri 2/6/2004 9:00 AM
To: [EMAIL PROTECTED]
Cc:
Subject: RE: Help with query
Hi Evelyn,
How would I do that - would something like this be what you had in mind?
left join categories o on o.CategoryID
]
Sent: Friday, February 06, 2004 8:53 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Help with query
You will need parentheses around the 'or' clauses of your where
clause.
You also don't seem to join the categories table with any
On 6 Feb 2004, at 14:38, Erich Beyrent wrote:
This seems really efficient, since the only large number of rows to
search against is the main listings table, if I read this right. Is
there any further optimization that I can do, or this as good as it
gets? Believe me, I am NOT complaining!!!
Yes,
This is basic, but need help anyway.
I am using PHP and Mysql on a webpage. Here
is my query which works:
$query = SELECT empnum,name,hdate,Photo,(YEAR(Curdate()) - YEAR(hdate)) as
timein FROM emp2 where mo
nth(hdate)=$mymonth order by timein;
now I need to modify it so that it ignores all
101 - 200 of 286 matches
Mail list logo
