One thing I don't understand is what MySQL tried to do first.
Why did MySQL chose co.name as a key?
On 3/3/07, Filip Krejci <[EMAIL PROTECTED]> wrote:
> SELECT *
> FROM employees2skills e2s FORCE INDEX (mod_time )
> INNER JOIN companies c ON c.id = e2s.co_id
> ORDER BY e2s.mod_time limit 5;
Yes
SELECT *
FROM employees2skills e2s FORCE INDEX (mod_time )
INNER JOIN companies c ON c.id = e2s.co_id
ORDER BY e2s.mod_time limit 5;
Yes, this query will be probably the best.
This go through mod_time index (which is already sorted) from begin and
try to join companies.
When row count achieve
I agree that my testing data is wrong, but
what I was wondering is why the query gets slow only when using join,
although without join it gives me lightning answer.
BTW, you gave me a big hint!
I didn't know about 'cardinality', so searched on the web with the
word 'cardinality',
which leads me t
It,s simple.
Look at cardinality on last two rows of statement 'show index from e2s'.
You have same cardinality on co_id and mod_time both.
My solution stands on the fact that mod_time will have much higher
selectivity in real data than co_id. (IMO)
So, I suppose that you have wrong testing
Thank you for answering.
I tried your suggestion and it's got fast, but still slow and
could not get rid of "Using temporary; Using filesort".
I don't understand why this simple query is so slow...
---
Hi,
if your selectivity is very low, try to use multiple index on e2s(co_id,
mod_time) and force this index in query.
Filip
employees2skills:
INSERT INTO employees2skills SELECT id, 1, 2, NOW() FROM employees;
# Yes, 100 have same skill_id, co_id, and even mod_time.
# selectivity is ex
Additional information.
MySQL Version: 5.0.33
Platform: Mac OS X 10.4.8
Machine Spec: 2.16GHZ Intel Core 2 Duo Memory 2GB MHZ SDRAM
Test was done using the following dummy data.
employees table:
20 ascii chars as employees' name
skills:
INSERT INTO skills
(name)
VALUES
('C'),
('C++'),
('C#'),
Hi list,
I have 4 simple table.
---
CREATE TABLE employees(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE skills(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
Hello.
Please could you provide a repeatable test case for this issue? FT
search works fine for me even if one of the columns has empty values.
'Yemi Obembe wrote:
> using the a sql statement like ds:
> select *, match(url, title, comment) against ('movies') as score from dir
> whe
using the a sql statement like ds:
select *, match(url, title, comment) against ('movies') as score from dir
where match(url, title, comment) against ('movies')
where dir is a fulltext table of url, titlke and comment
i however found out that if the comment column is empty it will return an
Yemi Obembe wrote:
If for example I have a table with the 3 columns: name,age,sex & I
build an index table
"index table" ? There is no such thing.
on the column 'name'. how can i get result of
the other rows (i.e age and)
other rows? You mean other columns?
from the main table
What main tabl
If for example I have a table with the 3 columns: name,age,sex & I build an
index table on the column 'name'. how can i get result of the other rows (i.e
age and) from the main table if i 'select' a name from the main table?
In short, what is the structure(or should i say syntax?) of a select
If for example I have a table with the 3 columns: name,age,sex & I build an
index table on the column 'name'. how can i get result of the other rows (i.e
age and) from the main table if i 'select' a name from the main table?
In short, what is the structure(or should i say syntax?) of a select
"Nick Zukin" <[EMAIL PROTECTED]> wrote on 03/14/2005 05:22:38 PM:
> I'm trying to do a multitable query and am having problems.
>
> I have three tables: vendors, products, and vendorproducts. The
> vendorproducts table creates a many to many relationship between the
vendors
> and the products. T
Thanks. That was it. I'm glad it was something so simple. Just needed a
careful set of eyes. I was worried my understanding of SQL/JOINs was
screwy.
Thanks again.
Nick
PS I'll read up on the FULLTEXT matching. I don't know it well.
>> $query = "SELECT v.vbusiness, v.vcategory, v.vurl, v.v
Nick Zukin wrote:
I'm trying to do a multitable query and am having problems.
I have three tables: vendors, products, and vendorproducts. The
vendorproducts table creates a many to many relationship between the vendors
and the products. There is nothing more than the vendor and product ids in
the
List
Asunto: Re: Multi-Table Query Problem...
On Mon, 14 Mar 2005 15:12:18 -0800, Scott Klarenbach wrote
> Because, with the '%keyword%' operator, you're going to match any of
> those columns that contain the keyword inside of it. This can be a
> little confusin
On Mon, 14 Mar 2005 15:12:18 -0800, Scott Klarenbach wrote
> Because, with the '%keyword%' operator, you're going to match any of
> those columns that contain the keyword inside of it. This can be a
> little confusing as 'ef' will return true on 'abcdefghijk'? Instead,
> you might try 'keyword%'
Because, with the '%keyword%' operator, you're going to match any of
those columns that contain the keyword inside of it. This can be a
little confusing as 'ef' will return true on 'abcdefghijk'? Instead,
you might try 'keyword%' so that 'apple' returns true for 'apples',
'apple juice', 'apple ci
What are the results?
sk
On Mon, 14 Mar 2005 14:22:38 -0800, Nick Zukin <[EMAIL PROTECTED]> wrote:
> I'm trying to do a multitable query and am having problems.
>
> I have three tables: vendors, products, and vendorproducts. The
> vendorproducts table creates a many to many relationship between
I'm trying to do a multitable query and am having problems.
I have three tables: vendors, products, and vendorproducts. The
vendorproducts table creates a many to many relationship between the vendors
and the products. There is nothing more than the vendor and product ids in
the vendorproducts tab
On Fri, 28 Jan 2005, Santino wrote:
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten good
results with the following version (it breaks
At 15:50 -0800 27-01-2005, cristopher pierson ewing wrote:
Shawn,
Okay, it turns out that I can solve my problem by reordering the
elements of the WHERE clause at the end of the query I sent before.
I've gotten good results with the following version (it breaks all
the fields in the Fulltext sea
Shawn,
Okay, it turns out that I can solve my problem by reordering the elements
of the WHERE clause at the end of the query I sent before. I've gotten
good results with the following version (it breaks all the fields in the
Fulltext search into separate searches):
SELECT
t1.course_id,
t1.
Shawn,
Thanks for the reply.
Here's the output of "SHOW CREATE TABLE" for one of the tables in
question:
CREATE TABLE `tblcourseextrainfo` (
`course_id` varchar(6) NOT NULL default '',
`course_description` text,
`course_intended_audience` text,
`course_keywords` text,
PRIMARY KEY (`cou
cristopher pierson ewing <[EMAIL PROTECTED]> wrote on 01/27/2005
04:01:22 PM:
> I'm running a query that pulls information from about six different
tables
> in a DB. I'd like to be able to do a fulltext search on fields in
several
> different tables. The end result should be that any row wi
I'm running a query that pulls information from about six different tables
in a DB. I'd like to be able to do a fulltext search on fields in several
different tables. The end result should be that any row with a fulltext
match in any of the fields in any table gets returned. I've tried a
syn
Subject: RE: Poor Select speed on simple 1 table query
>
> At 03:32 PM 11/15/2004, Donny Simonton wrote:
> >Mos,
> >Personally, I never use like for anything. I would add a fulltext index
> >myself and call it a day. But that's me.
> >
> >Donny
>
>
Rcd_Id.
Mike
> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Monday, November 15, 2004 2:40 PM
> To: [EMAIL PROTECTED]
> Subject: Poor Select speed on simple 1 table query
>
> It doesn't get any simpler than this. :)
>
> The Select statement take
Poor Select speed on simple 1 table query
>
> It doesn't get any simpler than this. :)
>
> The Select statement takes way too long to complete.
>
> select rcd_id, company_name from company where company_name like "fra%"
> 12357 rows fetched (86.08 seconds)
>
&
It doesn't get any simpler than this. :)
The Select statement takes way too long to complete.
select rcd_id, company_name from company where company_name like "fra%"
12357 rows fetched (86.08 seconds)
However if it returns just the column value from the index, it is quite fast:
select company_name
Justin French wrote:
Can anyone help me optimise the way I do this?
I have two tables, 'article' and 'user'. Article has a user_id which
related to the user table.
When selecting all articles from the the article table, I'd like to be
able to get the username of the user_id. Currently I'm doi
Can anyone help me optimise the way I do this?
I have two tables, 'article' and 'user'. Article has a user_id which
related to the user table.
When selecting all articles from the the article table, I'd like to be
able to get the username of the user_id. Currently I'm doing this as a
separate
> Thanks for posting about the command window sizes. It's been
something
> that I have frustratingly accepted in silence for a while now
> (especially having a 20 inch monitor at work).. Hooray!! I can finally
> get some horizontal real estate!!
+1 !
I think this should be documented somewhere !
es of buffer also gets me a
little weak at the knees too ;)
Regards,
Lachlan
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, 6 August 2004 11:14 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Table query and column overlap
You need to chang
You need to change the buffer size of your, for lack of a better term,
"DOS prompt window".
RIGHT-click on the title bar of your window and click on properties.
On the properties page for my platform (win2k) I have a Layout tab. On
that tab are two "size" settings, Screen Buffer Size and Window
Sorry guys, I forgot to post to the list as well..
-Original Message-
From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED]
Sent: Friday, 6 August 2004 4:12 PM
To: [EMAIL PROTECTED]
Subject: RE: Table query and column overlap
Hi again,
To fix this wrapping you can use the \G command, this
Dear Friends,
I am using mysql 4.0.17-nt
I have pasted structure of table below, while managing through command prompt
using sql without GUI.
Once I use without GUI, via command prompt
select * from quiz
to see contents of full table in each column
I get overlap of columns as pasted below,
At 23:13 +0200 5/2/04, Alessandro Sappia wrote:
When I create a table with InnoDB there is a strange behaviour in it:
mysql> CREATE TABLE prometeo_indirizzi_cliente (
-> indirizzi_cliente_id int(10) unsigned NOT NULL auto_increment,
-> denominazione_indirizzo_id int(10) unsigned default
When I create a table with InnoDB there is a strange behaviour in it:
mysql> CREATE TABLE prometeo_indirizzi_cliente (
-> indirizzi_cliente_id int(10) unsigned NOT NULL auto_increment,
-> denominazione_indirizzo_id int(10) unsigned default NULL,
-> cliente_id int(10) unsigned defa
> If you are unsure you should make a copy of the table and perform you test
> actions on that table. The answer is yes you can convert your unsigned
integer
> column to a varchar column.
Yep, first make a backup copy of your table, then alter the column to
varchar type.
To convert the existing dec
If you are unsure you should make a copy of the table and perform you test
actions on that table. The answer is yes you can convert your unsigned integer
column to a varchar column.
On Mon, 05 Apr 2004, joe collins wrote:
>
> I have a table that has a column :
> session_id INTEGER UNSIGNED NOT
I have a table that has a column :
session_id INTEGER UNSIGNED NOT NULL,
I now want to store a hexadecimal value in this column instead.
the column already contains values for some records.can I alter the data
type of this column (make it a varchar) without loosing the data already
stored?
o
I have 3 tables and want data from the first 2, but only want data from the third on
the rare times that there is a match in tables 2 & 3. If the conditions are not met in
table 3, I don't get the results i still want from table 1 & table 2.
Is there any way to do this in one query, without hav
looks like it's using the correct indicesI don't know what else
to do...
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 27, 2003 12:23 AM
To: Twibell, Cory L
Cc: [EMAIL PROTECTED]
Subject: Re: Table Query taking WAY TO LONG...HELP PLEA
On 26 Aug 2003 at 19:38, Twibell, Cory L wrote:
> I have a query that is inner joined with another table based on
> country codes Select distinct Name.* from Name inner join Location on
> Location.key = Name.key and Location.cc in (' here>');
>From the message you're getting it seems you're using
On Tue, Aug 26, 2003 at 07:38:04PM -0600, Twibell, Cory L wrote:
> All,
>
> I have a query that is inner joined with another table based on
> country codes Select distinct Name.* from Name inner join Location
> on Location.key = Name.key and Location.cc in (' here>');
>
> The problem is when I ha
AIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Twibell, Cory L" <[EMAIL PROTECTED]>
Sent: Tuesday, August 26, 2003 7:19 PM
Subject: Re: Table Query taking WAY TO LONG...HELP PLEASE!!!
> * Twibell, Cory L
> > I have a query that is inner joined with another table based
* Twibell, Cory L
> I have a query that is inner joined with another table based on
> country codes
> Select distinct Name.* from Name inner join Location on Location.key =
> Name.key and Location.cc in ('');
>
> The problem is when I have more than 2 country codes, the query
> takes forever..
All,
I have a query that is inner joined with another table based on country codes
Select distinct Name.* from Name inner join Location on Location.key =
Name.key and Location.cc in ('');
The problem is when I have more than 2 country codes, the query takes forever...
When I show processlist, it
* Mike Morton
> Runnint Mysql 3.23.47
>
> I have the following two tables:
> backorder_notification
> ->product_code varchar
> ->email varchar
> ->date_added date
> Products
> ->code varchar
> ->name varchar
> ->other stuff that is not important
>
> Essencially - I want t
Runnint Mysql 3.23.47
I have the following two tables:
backorder_notification
->product_code varchar
->email varchar
->date_added date
Products
->code varchar
->name varchar
->other stuff that is not important
Essencially - I want to get a count of the product codes that a
Has any of the MySQL gurus here made any amendments to Oscommerce's DB structure
or added any tailored queries? I have custom made 26 shipping modules for
certain shipping specifications and now I have another one to cater for!
product category type. The problem with this is that the product cate
On Thu, Jun 19, 2003 at 02:24:52AM +0530, gamin wrote:
> Hi,
>
>What would happen to my_table (~70MB of indexes) and its indexes
>if someone kills a an optimize table query - 'OPTIMIZE TABLE
>my_table' . Im wondering if i should provide a cancel button in
&
Hi,
What would happen to my_table (~70MB of indexes) and its indexes if someone kills a
an optimize table query - 'OPTIMIZE TABLE my_table' . Im wondering if i should provide
a cancel button in my application during the optimization period. It is generally more
user friendly to p
The solution depends on which version of MySQL you are using. If you
are using 4.1, you the easiest solution is to use a sub-select.
Something like this may work:
SELECT m.member_number, m.name, s.status, s.date
FROM members AS m, status AS s ON
WHERE m.member_number = s.member_number
AND m.m
On Sunday 15 June 2003 08:44 am, wrote:
> Greetings-
>
> I request your help constructing a query to return a list of active members
> based on the following:
select distinct members.members_number, members.name, status.status from
members,status where status.status='Active' and status.date
Greetings-
I request your help constructing a query to return a list of active members based on
the following:
CREATE TABLE Members(
member_number int unsigned AUTO_INCREMENT,
name varchar(25),
PRIMARY KEY(member_number))
CREATE TABLE Status(
member_number int unsigned AUTO_INC
Hi Ed,
>What query would I use to check for an existence of a table within a
>database?
SHOW TABLES LIKE '';
might help.
prosit
Klaus
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
MAIL PROTECTED]>
Cc: "Tarik Kutluca" <[EMAIL PROTECTED]>
Sent: Friday, January 17, 2003 12:05 PM
Subject: Re: dynamic table query
> * Tarik Kutluca
> > 1. I need to store dynamic sized tables with different row and
> > column sizes, but all small tables ( like
* Tarik Kutluca
> 1. I need to store dynamic sized tables with different row and
> column sizes, but all small tables ( like 2*5, 3*1,5*3)
> 2. For this purpose i created a table to store each value of the dynamic
> sized table, also with the row and the column number, so this is something
> like
>
Hi all,
1. I need to store dynamic sized tables with different row and column sizes,
but all small tables ( like 2*5, 3*1,5*3)
2. For this purpose i created a table to store each value of the dynamic
sized table, also with the row and the column number, so this is something
like
aij for an i*j ta
I have a table that is populated with raw data on a pass by an
extraction program. Its other fields are left NULL and a secondary
program parses through this data to fill in the other fields. I would
like to move some of this logic out of the second program and into SQL
statements, but it se
* Wilbert Enserink
> I'm using this query:
>
> CREATE temporary TABLE temptable SELECT * FROM gallery INNER JOIN
> designers
> ON (designers.designersID=gallery.designersID) WHERE
> (LOWER(gallery.omschrijving) LIKE '%$search%' OR
> LOWER(gallery.productnaam)
> LIKE '%$search%' OR LOWER(voornaam)
rsday, July 04, 2002 7:38 AM
Subject: temporary table query
> hi all
>
>
>
> I'm using this query:
>
> CREATE temporary TABLE temptable SELECT * FROM gallery INNER JOIN
designers
> ON (designers.designersID=gallery.designersID) WHERE
> (LOWER(gallery.omschrijving)
hi all
I'm using this query:
CREATE temporary TABLE temptable SELECT * FROM gallery INNER JOIN designers
ON (designers.designersID=gallery.designersID) WHERE
(LOWER(gallery.omschrijving) LIKE '%$search%' OR LOWER(gallery.productnaam)
LIKE '%$search%' OR LOWER(voornaam) LIKE '%$search%' OR LOWE
On Tue, Apr 09, 2002 at 09:38:47AM -0500, Aman Raheja wrote:
> I want to alter 50 tables, and for each the names start with the
> string 'mak' Is there an SQL query, so that I can do the change in
> one command.
Nope. You need an outside programming language to do it.
Jeremy
--
Jeremy D. Zawo
Hello everyone
I want to alter 50 tables, and for each the names start with the string
'mak'
Is there an SQL query, so that I can do the change in one command.
I have tried using wildcards - they don't seem to work in SQL.
Thanks in advance.
Aman
__
>>>When I run a query "SELECT count(*) as count FROM tbl where col_1 like
'value1'", the query can take up to 45 minutes to bring back a result if
there are
>>>large number (sometimes 1 million plus) of records that match 'value 1'.
Use EXPLAIN to make sure that you're using an index that you h
I'm trying to get a handle on how an increase inf physical memory will
affect the query time for a large table. Here's the set up.
I'm running MySQL 3.23.37 on RedHat 6.0.
The server has a 700Mhz process with 1.5 Gig of RAM.
I'm querying a table with 47 million plus records that has 3 columns (
e.
Regards
Quentin
-Original Message-
From: Q [mailto:[EMAIL PROTECTED]]
Sent: Friday, 2 November 2001 4:40 p.m.
To: [EMAIL PROTECTED]
Subject: a bug or intentional? -- problem with multiple table query
I am having a problem with a query and I don't know if it is a bug or
intentio
or intentional? -- problem with multiple table query
I am having a problem with a query and I don't know if it is a bug or
intentional. Anyway any input is greatly appreciated. Here is a basic
query
that is simpler than what I am dealing with but should demonstrate my
problem.
Lets say:
SE
I am having a problem with a query and I don't know if it is a bug or
intentional. Anyway any input is greatly appreciated. Here is a basic query
that is simpler than what I am dealing with but should demonstrate my problem.
Lets say:
SELECT A.1, A.2 FROM A
yeilds:
test1 test2
then:
SELECT
Well it is Cable Plant not Cable Planet.
abhishek sahay wrote:
> STEP 1: status ->SUCCESS
> ---
> mysql> CREATE TABLE pod_rating select h.he_headend_id 'Cable
> Plant',sk.tf_air_date,d.em_dma DMA,d.em_tv_households 'Households
> TVs',d.em_us_percent Population,p.tf_title 'Program Name',sk
STEP 1: status ->SUCCESS
---
mysql> CREATE TABLE pod_rating select h.he_headend_id 'Cable
Plant',sk.tf_air_date,d.em_dma DMA,d.em_tv_households 'Households
TVs',d.em_us_percent Population,p.tf_title 'Program Name',sk.tf_air_time
Time,st.tf_station_call_sign Network from prog_rec p,stat_rec
At 9:03 AM +0545 7/18/01, Deependra B. Tandukar wrote:
>Greetings !
>
>I have following tables in MySQL:
>
>TableItem
>idlist
>1 Banana
>2 Orange
>3 Mango
>
>Other tables are:
>Banana, Orange, Mango
>
>Instead of selecting Table Banana directly I need to select it as "select
>$Tabl
Greetings !
I have following tables in MySQL:
TableItem
idlist
1 Banana
2 Orange
3 Mango
Other tables are:
Banana, Orange, Mango
Instead of selecting Table Banana directly I need to select it as "select
$TableItem.list where id=1" or something like that for editing or deleting
How can I write a query that answers the following question: Which stores
have not entered soups in the collection_schedule for the current day? I
just want to get a list that shows the stores.code, stores.name,
stores.phone of all the stores that failed to update the soups for the day.
collect
On 2001 Jul 10, Yan Zhang <[EMAIL PROTECTED]> wrote:
> Please help me here, and let me know if MySQL can do it or not. All people
> around me told it should work in SyBASE.
Please don't spam! Use *one* e-mail address that is appropriate.
This is on our list of things to do, probably in version
No, MySQL does not support subquery so you need work around (not use)
it, by creating temp tbl and/or join etc. Think it a diff. SQL style.
Yan Zhang wrote:
>
> Please help me here, and let me know if MySQL can do it or not. All people
> around me told it should work in SyBASE.
>
> Yan Zhang
>
Please help me here, and let me know if MySQL can do it or not. All people
around me told it should work in SyBASE.
Yan Zhang
[EMAIL PROTECTED]
>>
I have two tables:
mysql> show tables;
+--+
| Tables_in_vendor
81 matches
Mail list logo
