Thanks for the leads. I'll double check my indices and check out the
following links.
> http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
> http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To u
I have a 400mb database. The first query to tables takes about 90 seconds.
Additional queries take about 5 seconds. I wait a while and run a query
again; it takes about 90 seconds for the first one and the rest go quickly.
I'm guessing data is being loaded into memory which is why things speed up
pecify a search criteria for each table separately,
> you
> can do it for each table in the (select ... where ...) and if you want to
> specify a search criteria for all the records of those unions, you can do
> it
> in a final where ... that's outside of those params.
>
&g
Implementation Group
> Virginia Tech Information Technology
> 1700 Pratt Drive
> Blacksburg, VA 24060
>
> Email: [EMAIL PROTECTED]
> Phone: (540) 231-4396
>
> -Original Message-
> From: Ed Lazor [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 26, 2007 4:37 PM
Thanks for the info Jerry. =)
> -Original Message-
> From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 26, 2007 1:59 PM
> To: 'Ed Lazor'; 'Octavian Rasnita'; mysql@lists.mysql.com
> Subject: RE: select statement with variable for ta
has
something in there or not. Improving on this idea would be finding a way to
just query the relevant tables... some sort of conditional union. Any
ideas?
-Ed
> -Original Message-
> From: Octavian Rasnita [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 26, 2007 1:02 PM
> To: E
Is there a way to get something like this to work?
Set @tname="mytable";
Select * from @tname;
Here's what I'm trying to really accomplish in case there is yet another way
to approach this...
I have to work with product data from multiple databases and multiple
tables. For example, one databas
Whew, thanks Jon =)
-Original Message-
SELECT product_lines.* FROM product_lines LEFT JOIN
manufacturer_product_line_index ON
manufacturer_product_line_index.product_line_id = product_lines.id WHERE
product_lines.id IS NULL
--
MySQL General Mailing List
For list archives: http://list
Three tables like this:
--
product_lines
--
id
title
--
manufacturer
--
id
title
--
manufacturer_product_line_index
--
id
product_line_id
manufacturer_id
The index provides a one to many relationship - one product line can
+ice +blue"
I'm fairly sure that's in the FULLTEXT search portion of the
online docs.
Chris
Ed Lazor wrote:
>Hi,
>
>How can I limit the results of a fulltext search to the
entries that only
>have the keywords I'm searching for?
>
>I'm searching the ti
Hi,
How can I limit the results of a fulltext search to the entries that only
have the keywords I'm searching for?
I'm searching the title field of a product database and I only want results
if the title has all of the words specified. I tried putting the word AND
between each word and mysql res
Thanks, Shawn. I didn't think count would just limit to the items being
grouped - very handy =)
-Ed
> SELECT URL, count(1) as popularity
> FROM yourtablename
> GROUP BY URL
> ORDER BY popularity DESC
> LIMIT 50;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
I use a table to log what pages on the website are getting visits with a
table structure like this:
ID
DateAdded
URL
Now I'm trying to query the database to see which URLs are most popular, but
I'm not sure how to go about doing this. Any ideas?
Thanks,
Ed
--
MySQL General Mailing List
For
> -Original Message-
> what we want is the value for the name field corresponding to the row
> with the most recent close_date.
Based on that comment, I'd
select name from TD order by close_date DESC limit 1
>
> something like this:
>
> select max(close_date), symbol, name from TD wh
Would UNION help? Something like this:
select count(ID) as Total from categories where ID > 5 UNION
select count(ID) as Total from products where ID > 5 UNION
select count(ID) as Total from systems where ID > 5
Then you could just sum Total?
> -Original Message-
> They do not... But
Is there a way for me to change this select query into an update query?
select t1.ID, t1.Title, t2.Title from products t1, systems t2 where t1.Title
like CONCAT(t2.Title, "%")
A lot of products have the system title as the first part of the product
title. I'm trying to update the product SystemI
Interesting. I thought you could sort by aliases. Thanks Shawn.
The easy answer was to just add the calculation to the where section as
well. But which approach is faster - "having" or the calculation?
Ie.
select ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as
discount from
I'm getting an unknown column error for discount with the following query.
Any idea why?
-Ed
SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as
discount
FROM `products`
where discount > '10'
limit 10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/
Doh... Guess I spoke too soon. I get it now. I wasn't seeing
a.CategoryID=b.ID
=)
> -Original Message-
> select a.ID
> from products a, categories b
> where a.CategoryID=b.ID and (b.ID='21' OR b.ParentID='21')
--
MySQL General Mailing List
For list archives: http://lists.mysql.co
most never use LEFT and RIGHT joins, thus, the
> syntax MIGHT be wrong (I
> was too lazy to check in the manual :) ) - I'm not a pro in
> MySQL, so if I explained something
> wrong, I hope the more experienced members will correct it.
>
>
>
> Remi Mikalsen
>
Hi Everyone,
I got excited when I discovered subselects, but quickly discovered that 4.1
is still in gamma and I can't put it onto my production server. The query I
wanted to use would be great, so maybe there's a way to convert it - since
the manual says most subselects can be done with joins.
Have you checked the mysql log files in c:\mysql\data?
> -Original Message-
> From: Dominic James [mailto:[EMAIL PROTECTED]
> Sent: Thursday, October 07, 2004 10:01 PM
> To: [EMAIL PROTECTED]
> Subject: 2003 server problem
>
> I am having trouble intalling versions 4.02 and 4.1 onto serv
Have you tried a more simple query to make sure that communication between
your application and the database is working properly?
Have you tried a variable name for 'Tick Date' that does not include spaces?
-Ed
> -Original Message-
> Hello all.. I'm running into a little bit of a proble
Are you talking about user authentication for the purpose of people
accessing the MySQL database or are you talking about user authentication in
terms of people accessing restricted areas on a website?
> -Original Message-
> I have never set up a web site running a mysql server,
> so I am
> -Original Message-
> From: Christopher L. Everett [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 06, 2004 1:47 AM
> To: Mysql List
> Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
>
> I have an application where I create a faily large table (835MB) with a
> fu
> Ed,
>
> When you *reply* to a message, most mail clients (including yours) add a
> header
> like this:
>
>In-Reply-To: <[EMAIL PROTECTED]>
>
> That stuff in between the < and > is the message-id of the replied-to
> message.
>
> This tells the recipient's email client that your message is
> -Original Message-
> - If you use one word in your search, 1 is a probable score, because all
> the results that
> appear have the same relevance (they all contain that word!).
> - If you use two words, where the second isn't present in all results, you
> shouldn't get
> relevance value 1
Outlook 2003 here and its working just like Scott's (Converation, Subject,
etc.).
> -Original Message-
> If you sort it by conversation topic, then it will seem to group by
> "threads".
> I'm running Outlook 2000.
>
> Cause your doesnt support threads.. Outlook was the only one I k
> -Original Message-
> Actually its proper email etticate.. look it up if you don't believe me..
That sounds like a copout. Could present formal references to back this up?
I'm trying to substantiate your claims, but a Google search failed to bring
up anything relevant when searching wit
> -Original Message-
> Try this:
>
> select column1, match(column1) against ('+orange -fruit' IN BOOLEAN MODE)
> as score
> from some_table where match(column1) against('+orange -fruit' IN BOOLEAN
> MODE)
> order by score desc
>
> this way you have your results ordered by relevance, and y
ginal Message-
> On Tuesday 05 October 2004 01:14 pm, Ed Lazor wrote:
> > Does anything need to be done to my data while upgrading the server from
> > 3.23 to 4.0.21?
>
> Whats the deal and this list? No one can ever just hit new message, they
> always hit reply and pu
Does anything need to be done to my data while upgrading the server from
3.23 to 4.0.21?
Thanks,
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way to have boolean fulltext searches and still have results
automatically sorted in order of decreasing relevance?
It would be nice to enter a search for
+orange -fruit
And have the results come back in order of decreasing relevance.
Thanks,
Ed
--
MySQL General Mailing
Hi,
What's the best way to run a fulltext search when several fields from a
single table are involved?
I have a database of products with fields for Title, Summary, and
Description. The first two fields are varchar and the third is text. An
FULLTEXT index is created for the Description fi
> And here is my.ini
>
>
>
> [MYSQL-API]
>
> user='user'
>
> password='**'
>
> Server='chilton.***.edu'
>
> database='machine'
>
>
>
>
>
> the * are there for the purpose of this email only.
Even though you put *** in the server address, I bet it's
Server='chilton.BYU.edu'
I figured I'd post a follow-up to the discussion earlier this week on issues
relating to storing images in MySQL.
In my opinion.
Whether you store images in MySQL ultimately depends on whether you can
setup a caching accelerator (like Squid) between you and your visitors.
Storing images in th
You are correct. It's not necessary to change your SQL statements to take
advantage of indices.
Redefining your tables may not be necessary, but I can see areas where you
might see benefits.
One example would be the stateVAL field in the first table. It looks like
you're storing an abbreviati
> Um.. Are you serious? thats all you do, create indexes?
Ok, I'm assuming familiarity with efficient schema design since joins are
being used. I'm excluding factors like system hardware, system load, OS,
MySQL performance tuning, etc..
And by efficient schema design, I'm referring to proper sep
I read through the article and ran some more tests. The new scripts and
tables provide similar initial latency, but I think the test results show
them to be faster overall.
When it comes to latency, direct file access is still the champion without
caching. I think you made a good point about thr
I usually create an index for each criteria being checked against in the SQL
statements.
For example, for this query
Select * from products where ProductID = 'aeg8557'
I'd create an index on ProductID. The same thing applies if you're pulling
data from multiple tables.
For this query:
Selec
Thanks for the article. I'll check it out.
Throughput of 4600K/s is great. How's latency?
-Ed
> -Original Message-
> So if the image was say 200K in size, the metadata for the image would
> be 1 row in a table, and the image data would be 4 rows in the data
> table. 3 full 64K rows +
Ok... I have some more test results. After optimizing as much as I can
think of, without using caching, I've gotten things down to a 13x
difference. Using Apache's ab performance test, the image comes from a file
at an average of 2ms and from the database (using PHP4) at an average of
28ms.
I kn
sed - don't the images get cached
as actual files? If so, the original method of storage would be a mute
point. That's what I'm trying to find out. What do you think?
-Ed
> -Original Message-
> From: Eric Bergen [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, Se
> -Original Message-
> I have heard also that it helps to keep the table with the blobs having a
> low number of fields. Like just a primary key and the blob field. Have
> all your other metadata in a seperate table. Especially if you are going
> to be occasionally doing queries of just
Try mysql_escape_string instead of addslashes.
Also, I'm not sure why you did it, but you don't need to include your entire
form in the PHP script and echo it. If you have PHP code before and after
the form, you could use this approach:
Form
You could also try escaping the title all of the
What do you mean "chunked"?
I figured it would be easier to track about 32,000 images in MySQL than in
files, so I setup a test to see what the performance difference is and if
storing in MySQL would actually work.
Everything is working and it's a lot easier to keep track of the images in
MySQL.
Hi =)
How would you approach the design of a database that models the
following information?
- Users
- Invoices
- Purchase Orders
- Sales Orders
- Adjustments
- Products
We were originally working with Users, Purchase Orders, Sales Orders,
and Products. Everything was pretty easy at first... I
Hi Charles,
Answers:
1. Frequent web viewers
2. Medium... internal website
3. An invoice will get frequent views while it's active, but very few
views after it's completed.
4. About 1200 entries a month.
Are there approaches other than what I described?
-Ed
-Original Message-
Your
Is there an abstract way to refer to field names in a result set?
For example:
select ID, Name, Price from Products order by field(1)
where "field(1) would be the abstract way of referring to the specific field to sort
on.
It's for a function that allows me to display tables and sort by colum
I'm not sure how to phrase this. Please excuse the crudeness of my description.
I'm storing calendar data in a MySQL database and I'm wondering, is there a
way to specify a day, such as the 28th, and "fourth" "Tuesday" as search
criteria when selecting rows from a table with a field of type dat
I'm trying to update a database. Here's the query that's not working. Is
there another way of doing it or is this impossible with MySQL?
Thanks,
-Ed
update SiteIndex
set
SiteIndex.Visitors = Pages.Visitors
where
SiteIndex.Title = Documents.Titl
I'm trying to update a database. Here's the query that's not working. Is
there another way of doing it or is this impossible with MySQL?
Thanks,
-Ed
update SiteIndex
set
SiteIndex.Visitors = Pages.Visitors
where
SiteIndex.Title = Documents.Titl
I'm getting a lot of this in my server log, any idea of what's causing it
or how to find out? I think they are occurring when my web server attempts
to communicate with the database server. It doesn't happen all the time,
but it's happening a lot and causing havoc with being able to view web
At 11:40 PM 1/29/2002 -0600, Dan Nelson wrote:
>In the last episode (Jan 30), Tshering Norbu said:
> > > Hi list,
> > > How do you suppress the WARNING message like the one here:
> > >
> > > Warning: stat failed for photos/p73n1.jpg (errno=2 - No such file or
> > > directory) in /ad/details.php on
I used to use some php scripts that allowed me to work on my database. I'm
trying to find them again... anyone know what they might have been called
or where to find them?
-Ed
-
Before posting, please check:
http://www.mys
At 12:51 PM 1/26/2002 -0800, you wrote:
>I Scotland,
>
>It's not the first time i give this adress:
>http://www.urbanresearch.com/software/utils/urbsql/
>
>Hope This Help ;)
>
>theOtherOne
It looks like urSQL allows you to view data from various database
formats. Does it handle data conversion
I found this:
SELECT * FROM table_name ORDER BY RAND()
Is there a way to weight the random selection so that certain records are
more likely to come up?
It's for a banner exchange program. The idea is to somehow give precedence
to help promote certain sites.
Thanks! =)
-Ed
Thanks to everyone who helped so quickly =) Based on what people said, I
was able to come up with a solution. The goal was to get a total number of
log entries from a given month and year. The query we came up with is:
select count(*) as Total from log where month(datestamp) = '$current_mont
Hi =)
Could you help? I have a table with a datetime field and I'm trying to
figure out how to format an SQL query to select records only from within
the current month. Any help would be greatly appreciated.
Thanks,
-Ed
-
Hi =) Could you help me with creating a proper query? I have a table that
stores messages with a field for the message id, thread, and parent. The
goal is to create a query that gives me the total threads. I read in a
book and found the DISTINCT key allows me to run a query like this:
sele
60 matches
Mail list logo
