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,
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++'),
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
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...
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
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
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
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,
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
where
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
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
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
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 table?
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. There is
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
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 the
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
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%' so
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 confusing as 'ef' will return true on 'abcdefghijk
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
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,
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
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
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
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 with a
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
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,
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
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)
However if it returns just the column value from the index, it is quite
[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 takes way too long to complete.
select rcd_id, company_name from company where
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
Donny,
Unfortunately I can't. The query must return all rows that
*start
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
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
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
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
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 change
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 !
I
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,
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 default
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
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?
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
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
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
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 ('list of countries here');
The problem is when I have more than 2 country codes, the query takes forever...
When
* 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 ('list of countries here');
The problem is when I have more than 2 country codes, the query
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 on
country codes
Select distinct Name.* from Name inner join
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 ('list of countries
here');
The problem
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 ('list of countries
here');
From the message you're getting it
|
It 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 PLEASE
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 are in
* 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 to get a count of
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
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
my application during
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 provide one
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
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
Hi Ed,
What query would I use to check for an existence of a table within a
database?
SHOW TABLES LIKE 'table_name';
might help.
prosit
Klaus
-
Before posting, please check:
http://www.mysql.com/manual.php (the
* 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
aij
]
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 2*5, 3*1,5*3)
2. For this purpose i created a table to store each value of the dynamic
sized
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
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
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
* 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) LIKE
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
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
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 have on
-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
intentional. Anyway any input
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:
? -- 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:
SELECT A.1, A.2 FROM
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
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
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
$TableItem.list
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.
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 |
+--+
| savedata |
| tradeday |
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
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
77 matches
Mail list logo
