Do I need to use GROUP BY to do this?

Hi,   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` (   `sales_id` int(11) NOT NULL auto_increment,   `

Re: How to select data if not in both tables?

the sale table 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_pric

How to select data if not in both tables?

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

Re: Column in field list is ambiguous

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 in field list is ambiguous

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 (asin,upc,isbn,ean,title,brand,manufacturer,mpn,model,artist,author,platform,format,label,studio,pu

Re: innodb questions

ad individual databases. Am I correct here? Thanks, Grant Ware 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 serve

innodb questions

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 fi

Re: Most efficient way to design this table

rs. Numeric keys take 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: &qu

Most efficient way to design this table

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 particular

Re: key_buffer_size vs innodb_buffer_pool_size

key buffer. On 1/14/06, 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

key_buffer_size vs innodb_buffer_pool_size

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 key_b

Can I do a boolean search and get the row count in 1 SQL query?

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) SEL

Can I go a boolean search and get the row count in 1 SQL query?

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 $

Re: Do I need to tweak my server variables for this SELECT statement?

se, but my-huge.cnf is a better 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

Re: Do I need to tweak my server variables for this SELECT statement?

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 t

Re: Do I need to tweak my server variables for this SELECT statement?

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 fr

Re: Do I need to tweak my server variables for this SELECT statement?

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. It

Re: Do I need to tweak my server variables for this SELECT statement?

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 pn

Re: Do I need to tweak my server variables for this SELECT statement?

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 try

Re: Do I need to tweak my server variables for this SELECT statement?

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 example

Do I need to tweak my server variables for this SELECT statement?

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

Can I weight full-text searches in boolean mode?

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

Can I weight full-text searches in boolean mode?

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 mat

Need help with fulltext search and left join

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, pn_coupons_coupons.co

SELECT help

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 t

QUERY ordering clarification

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 alw

Need help figuring out indexes for faster SELECT queries

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] VARCHAR

Need help optimizing full-text searches

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 minimu

Need help with mysql search engine

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 minimu

Insert data if not duplicate based on order

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