In this case, u require 2 indexes on table b.
1. WHERE b.basetype = 0 (requires index on b.basetype)
2. b.BoardID = m.BoardID (requires index on b.BoardID)
However, you are only allowed one index per table join.
Hence you need ONE composite index on table b with the fields b.basetype
and
pow wrote:
In this case, u require 2 indexes on table b.
1. WHERE b.basetype = 0 (requires index on b.basetype)
2. b.BoardID = m.BoardID (requires index on b.BoardID)
No, this requires an index on m.BoardID, which he already has and mysql is
using.
However, you are only allowed one index
Jack Lauman wrote:
I have the following query which display every Cuisine in the database
sorted by the WebsiteName.
How can I modify this to get a COUNT of the number of records in each
Cuisine in each WebsiteName?
SELECT DISTINCT Restaurant.Cuisine, RestaurantWebsites.WebsiteName
FROM
Follow is my sql statement:
-
SELECT SUM(ol_qty) sumolqty, ol_i_id
FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT
MAX(o_id)-1 FROM orders) AND
NOT
Follow is my sql statement:
-
SELECT SUM(ol_qty) sumolqty, ol_i_id FROM orders, order_line
WHERE orders.o_id = order_line.ol_o_id AND orders.o_id (SELECT
MAX(o_id)-1 FROM orders)
AND NOT
Sorry. While I am able to log in, I get the following:
| GRANT ALL PRIVILEGES ON *.* TO 'chris'@'localhost' IDENTIFIED BY
PASSWORD '*446CB892D3DFFDDC86BDDF26E4EB43158356DF64' WITH GRANT OPTION
|
after a restart, I get
| GRANT ALL PRIVILEGES ON *.* TO 'chris'@'localhost' IDENTIFIED BY
PASSWORD
hi,
i want to copy a whole record from one table to another
like below
insert into p1 values(select * from p2 where pono=1);
its giving me error
please help
regards
p rao
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
prathima rao wrote:
hi,
i want to copy a whole record from one table to another
like below
insert into p1 values(select * from p2 where pono=1);
its giving me error
please help
regards
p rao
You should always include the exact text of the error message to help us
diagnose your problem.
Hello all,
I'm storing data from a series of tests throughout each
24-hour period. I thought to create a table for each test.
(There are six tests, lots more cols per test, and many
users performing each test.)
select test1.date, test1.time, test2.date, test2.time from
test1 left join test2 on
Given the following query, how can it be modified to return 'Cuisine'(s)
that have no rows as having a count of zero and also return a SUM of
COUNT(*)?
SELECT w.WebsiteName, r.Cuisine, COUNT(*)
FROM Restaurant r
JOIN RestaurantWebsites w
ON r.RestaurantID = w.RestaurantID
WHERE w.WebsiteName =
Jack Lauman wrote:
Given the following query, how can it be modified to return 'Cuisine'(s)
that have no rows as having a count of zero and also return a SUM of
COUNT(*)?
SELECT w.WebsiteName, r.Cuisine, COUNT(*)
FROM Restaurant r
JOIN RestaurantWebsites w
ON r.RestaurantID =
Rereading his initial query, u are right. this is not a situation of not
having the right composite index.
Yup, u are counting many rows, and hence it will take awhile.
Michael Stassen wrote:
pow wrote:
In this case, u require 2 indexes on table b.
1. WHERE b.basetype = 0 (requires
Hi ,
This is a little off topic but I have seen count(*) on this list afew
times and it got me wondering...
Is there a reason to use SELECT COUNT(*) as opposed to SELECT COUNT(column)?
I have noticed that selecting count(*) versus specifying the column
name executes much more slowly.
I've
Chris Fonnesbeck wrote:
Sorry. While I am able to log in, I get the following:
| GRANT ALL PRIVILEGES ON *.* TO 'chris'@'localhost' IDENTIFIED BY
PASSWORD '*446CB892D3DFFDDC86BDDF26E4EB43158356DF64' WITH GRANT OPTION
|
This is a new, 4.1+, 41 byte password hash.
after a restart, I get
|
Issuing the grant command yields an error:
mysql GRANT ALL ON test.* TO [EMAIL PROTECTED] IDENTIFIED BY PASSWORD
'testing';
ERROR 1105 (HY000): Password hash should be a 41-digit hexadecimal number
Also, I tried deleting and re-creating the 'chris' user, and the same
problems arose. However, I
Chris Fonnesbeck wrote:
Issuing the grant command yields an error:
mysql GRANT ALL ON test.* TO [EMAIL PROTECTED] IDENTIFIED BY PASSWORD
'testing';
ERROR 1105 (HY000): Password hash should be a 41-digit hexadecimal number
That's my fault. That should have been
GRANT ALL ON test.* TO
Hi,
I have a table with a DATE type column and I want to search for more records
that have the same year and month.
I have tried searching with:
select ... where date_format(date, '%Y-%m')='2005-06' ...;
I know that if I apply a function to the date column, the index on that
column is not
Jonathan Mangin wrote:
Hello all,
I'm storing data from a series of tests throughout each
24-hour period. I thought to create a table for each test.
(There are six tests, lots more cols per test, and many
users performing each test.)
But each test is performed no more than once per day by a
Octavian Rasnita wrote:
Hi,
I have a table with a DATE type column and I want to search for more records
that have the same year and month.
I have tried searching with:
select ... where date_format(date, '%Y-%m')='2005-06' ...;
I know that if I apply a function to the date column, the index
I believe the difference is that count(*) includes nulls (because it is
counting the number of records), whereas count(column) only counts the
records where the column being counted is not null, regardless of the
total number of rows.
Hmm, on a related question then if I am correct above, does
stipe42 wrote:
I believe the difference is that count(*) includes nulls (because it is
counting the number of records), whereas count(column) only counts the
records where the column being counted is not null, regardless of the
total number of rows.
Right. COUNT(*) counts rows, COUNT(col)
stipe42 wrote:
Jack Lauman wrote:
Given the following query, how can it be modified to return 'Cuisine'(s)
that have no rows as having a count of zero and also return a SUM of
COUNT(*)?
I'm sorry, but I'm having trouble picturing what you are doing. What is a
Cuisine with no rows? I see
Sorry... I was having a brain fart. (I use entries in the web.xml file
to generate a dropdown list of cuisines). The field cuisine is part of
the restaurant table. And it does not accept a null value. It should
be split out into it's own table.
I do need to get a SUM of all the values
Jack Lauman wrote:
Sorry... I was having a brain fart. (I use entries in the web.xml file
to generate a dropdown list of cuisines). The field cuisine is part of
the restaurant table. And it does not accept a null value. It should
be split out into it's own table.
I do need to get a SUM
On Fri, 2005-07-15 at 13:28 -0700, David Griffiths wrote:
David,
Thanks for your suggestions, i'll give them a try.
There are other tuning choices (including the thread-pool-cache). The
best resource is the page on innodb performance tuning, and it can be
found here:
Hi everyone,
Im puzzling over why a query loads faster the second time I execute it.
I am sure it is not query cached, because that is off.
I also made sure that the key that is used was already cached b4 i even
executed the query the first time. So it is not like as if the 2nd
execution used
On 7/15/05, Bruce Dembecki [EMAIL PROTECTED] wrote:
This process has worked for us taking our latin1 4.0 databases and
turning them into utf8 4.1 databases. UTF8 data we had already
put in
our 4.0 database despite it's latin1 encoding was correctly exported
out of 4.0 and correctly
27 matches
Mail list logo