Hi,
nbsp; I have a table where I keep sales transactions, so I'm trying to do a
query that will count the number of transactions per day.
My test data looks like:
--
-- Table structure for table `sales_activity`
--
CREATE TABLE `sales_activity` (
nbsp; `sales_id` int(11) NOT NULL
is a subset of the product_table.
SELECT * FROM product_table p
LEFT JOIN sale_table s USING (prod_id)
WHERE s.prod_id IS NULL;
PB
Grant Giddens wrote:
Hi,
I have 2 tables like:
product_table:
prod_id
item_name
price
data
data
etc
sale_table:
prod_id
sale_price
If I want to select
Hi,
I have 2 tables like:
product_table:
prod_id
item_name
price
data
data
etc
sale_table:
prod_id
sale_price
If I want to select all the products that are in the product_table, but not in
the sale_table, how to make the query? The product_table has all the products,
but the sale table is
Thanks Michael, That was the solution. This was the first time I've seen that
error and now it makes plenty of sense.
Thanks,
Grant
Michael Stassen [EMAIL PROTECTED] wrote: Grant Giddens wrote:
Hi,
I'm using mysql 4.1.14 and and getting this error:
Column 'asin' in field list
Hi,
I'm using mysql 4.1.14 and and getting this error:
Column 'asin' in field list is ambiguous
This is the query that is giving me the error:
SELECT pn_pricecompare_searchdata.prod_id, MATCH
Adams [EMAIL PROTECTED] wrote: On Jan 31, 2006, at 9:54 PM, Grant
Giddens wrote:
Since changing these tables, I've noticed some large files in my /
var/lib/mysql directory. This is on my test server and I'm running
gentoo linux.
The files in this directory look like:
/var/lib/mysql
Hi,
I recently converted some of my tables to innodb from myisam. I don't need
transactions or rollback capability, I switched because I needed row-level
locking. These are large tables with many rows and lots of INSERTS and UPDATES.
Since changing these tables, I've noticed some large
less space and are faster.
Your Product_Feature table then may have:
- product_id INT unsigned
- feature INT unsigned
having a Primary Key of 8 bytes i.s.o. 141 bytes.
Thus a smaller index, less disk reads and more speed.
HTH, Cor Vegelin
- Original Message -
From: Grant Giddens
, Grant Giddens wrote:
Hi,
After reading through the example my.cnf files (large, huge, etc), I
started to wonder what the difference was between the isam key_buffer_size
and the innodb innodb_buffer_pool_size.
I realize that they are two different table types, but some of the docs
Hi,
I currently have a table with 2 columns, product_id and feature. Each
product_id can have multiple features.
My two columns are:
product_id is type char(13)
feature is type varchar(128)
In order to make sure I don't have the same feature listed twice for a
Hi,
After reading through the example my.cnf files (large, huge, etc), I
started to wonder what the difference was between the isam key_buffer_size and
the innodb innodb_buffer_pool_size.
I realize that they are two different table types, but some of the docs says
to set the
Hi,
I have a web app where I am doing a boolean search. I only want
to return 10 results per page, but I'd also like to know how many total
rows match the search query. I'm currently performing this with 2
query statements:
1. (To get the actual rows via the search)
Hi,
I have a web app where I am doing a boolean search. I only want to return
10 results per page, but I'd also like to know how many total rows match the
search query. I'm currently performing this with 2 query statements:
1. (To get the actual rows via the search)
SELECT
Thanks Hank. I'll try to split up the query into 2 separate queries. I think
that the reason the original query is so slow is that I don't have enough RAM
allocated to mysql. When the original query takes place, I see a process
Copying to tmp table on disk. I believe it's writing all
starting point than my.cnf.
James Harvard
At 6:45 am -0800 23/12/05, Grant Giddens wrote:
I think that the reason the original query is so slow is that I don't have
enough RAM allocated to mysql. When the original query takes place, I see a
process Copying to tmp table on disk. I believe it's
Hi James.
Thanks for the tips. I tried your below SQL call of quering just one
column. The query didn't speed up.
I think I am running out of RAM and thus caching the temp table to disk. My
server is currently using the default my.cnf file. I will try the large and
huge
Thank Hank. I will try this. When you say the table descriptions, do you
mean for me to post my CREATE TABLE syntax of how I created the table?
Thanks,
Grant
Hank [EMAIL PROTECTED] wrote: I don't think the problem is going to be
solved with the my.cnf file.
Here's what I would
OK guys,
Here is are my tables and test SELECT queries. Before every query I did a
FLUSH TABLES command. Before starting these test, I did and ANALYIZE and
OPTIMIZE on all the tables. Hopefully the formatting of this email isn't too
bad.
product table:
show create table
It'll be a few hours before I get home and can do this query with the
variations you mentioned. I don't know if I need the separate index key on
`salesrank` or not. I thought it would speed up my query be having it.
In the previous email I included the results from doing an EXPLAIN.
OK,
I tried the following queries:
1. Use the ORDER BY and not the LIMIT -- still slow
2. Use the LIMIT and not the ORDER BY -- fast
So it looks like it's building a whole temp table with all the items and then
doing the ORDER BY.
Just as a test, I removed the INDEX
Hi,
For the first time, I'm working with a really large database. I have 1
SQL statement that brings my server to it's knees. This setup is currently on
my home development PC, and not in production. The server is running apache,
samba, and mysql under gentoo linux. I'm the only
Hi,
I have a table with consumer products. I have a row product name
and brand.
If I do a full-text search in boolean mode for a term like apple
ipod I get results like:
Apple iPod (brand: Apple)
TuneDock for Apple iPod (brand: Belkin)
Since both of these terms match
Hi,
I have a table with consumer products. I have a row product name and
brand.
If I do a full-text search in boolean mode for a term like apple ipod I get
results like:
Apple iPod (brand: Apple)
TuneDock for Apple iPod (brand: Belkin)
Since both of these terms match
Hi,
I keep getting errors on this query and I'm not sure
why. I'm using mysql version 4.0.22.
Any ideas?
SELECT pn_coupons_store.store_name,
pn_coupons_store.store_name_short,
pn_coupons_coupons.store_id,
pn_coupons_coupons.coupon_id,
pn_coupons_dealtype.dealtype_name,
Hi,
If I do a query like:
SELECT prodname, price FROM prod_table WHERE sku in
($sku1, $sku2, $sku3, $sku4)
Will my results always be ordered in $sku1, $sku2,
$sku3, $sku4 order? I can't really do a ORDER BY
prodname or price here.
I just want to make sure that that this type of query
will
Hi,
I am tring to do a select from 2 tables.
Table1:
sku
title
Table 2:
sku
feature
SELECT table1.title, table2.feature FROM table1,
table2 WHERE table1.sku in ($sku1, $sku2, $sku3) ORDER
BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC
That seems to work to some extint, but I am getting
way
I have a new project I'm working for and I was
wondering if anyone could help me optimize my selects
for speed. I have a table with about 500,000 entries.
The table structure I'm using is (via my PHP
commands):
$sql = CREATE TABLE $store_data_table (
$store_data_column[sku]
Hi,
I currently have a table with 2 text fields, both of
which are marked as fulltext. I'm using the full-text
boolean search method with fairly good results.
My problems however are:
1. I am on a shared host provider so I can't tweak
the full-text search options such as stop words or
Hi,
I currently have a table with 2 text fields, both of
which are marked as fulltext. I'm using the full-text
boolean search method with fairly good results.
My problems however are:
1. I am on a shared host provider so I can't tweak
the full-text search options such as stop words or
Hi,
I have a table that has many rows. I want to be
able to insert a new row only if has a unique field of
the last 20 rows when I sort them by date.
Currently before I insert a new row, I will select a
specific field for 20 rows and run a for loop in php
looking for a match with the data I'm
30 matches
Mail list logo
