trouble sending mail to [EMAIL PROTECTED]
Has anyone else been having problems sending mail to mysql- [EMAIL PROTECTED] I tried it with two mail accounts (one at work and also GMail) I get this as a response...for some reason it doesn't seem to be getting to the list at all. The mail servers are somehow taking that address and turning it to [EMAIL PROTECTED] From work: [EMAIL PROTECTED] (lists.mysql.com: 550 mail to ^.+- [EMAIL PROTECTED] not accepted here (#5.1.1))Reporting-MTA: dns; mail.esidesign.com Arrival-Date: Fri, 14 Sep 2007 09:58:50 -0400 From GMail: Technical details of permanent failure: PERM_FAILURE: SMTP Error (state 13): 550 mail to ^.+- [EMAIL PROTECTED] not accepted here (#5.1.1) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY...not using index?
I have an index on `food` and on `active`, how come the result of the EXPLAIN doesn't show the query using an index? I'm concerned that as the query time will grow with the table. My Query: SELECT `food` , COUNT( `food` ) AS 'population' FROM `users` WHERE `active`=1 GROUP BY `food` LIMIT 0 , 25 Result of Explain: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE users ALL active_idx NULL NULL NULL 11382 Using where; Using temporary; Using filesort -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index, unique index question
I have a table that has a Primary key using the 'id' column. The table also has a 'receiver_id' and a 'sender_id'. I have queries that will use (1) WHERE receiver_id = or (2) WHERE sender_id= but never WHERE receiver_id='###' AND sender_id='###' Also, I want the receiver_id/sender_id pair to be unique. The reason I want this unique key is so that I can issue a 'INSERT ... ON DUPLICATE KEY UPDATE' kind of query. What's the best approach to create indices in this case? (A) Create an index on 'receiver_id' and also create an index on 'sender_id' ...and enforce the uniqueness of receiver_id and sender_id in code...first do a query to see if it's there then either do an UPDATE or and INSERT. or (B) Create a unique index on the 'receiver_id' and 'sender_id' pair? When I create both (A) and (B), phpmyadmin gives me a warning indicating that more than one index is created on 'receiver_id.' Any suggestions on how to handle this situation? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
Thanks David! This the kind of answer that I was looking for (more about general PHP and MySQL performance) I think b/c of the way the tables are designed, I have to perform multiple queries, unfortunately. I think I'll have to do some performance testing at some point. But for now I will let MySQL do the work instead of filtering with an IF in PHP. -James On May 10, 2007, at 12:20 PM, David T. Ashley wrote: On 5/9/07, James Tu [EMAIL PROTECTED] wrote: The database server and the web server are on separate machines. Table A contains a record for each user. Let's say Table B contains 'relationship' information. They can be of type 'friend' or 'family'. If a user knows another user, this relationship would be kept in this table, along with the type of relationship. Table B can get big. 10,000's or maybe 100,000's. I'm doing a query in PHP and want to end up with two arrays. One for type friend and one for type family. Which is better: (Method 1) Do ONE query for all the records that meet a certain criteria (let's say 'active'). Then use PHP to loop through the results and put each record into either the friend array or the family array. (Method 2) Do TWO queries. One just for friend. Loop through the records and put into friend array; Then do another query for family...and loop through again. In general, you don't want to introduce arbitrarily large result sets into PHP. PHP is fast, but there are memory limits and speed of iteration limits. In general, you want to structure things so that MySQL returns exactly the results you need, and in the order you need. In general: a)Check your database design to be sure that the queries you are interested in are O(log N). If not, make them that way, by rethinking your database design and/or adding indexes. b)See if you can get all the data you want in one query. In the example you gave, I think the WHERE clause syntax will allow checking for certain of an enumerated type, i.e WHERE (X=3 OR X=5) ... that kind of thing. So, retrieving friends and family in one query shouldn't be a problem. Two queries should not be required. Here is what you need to remember: a)Designs that aren't O(log N) for the queries you are interested in often catch up with you as the database grows. b)There is a speed hierarchy involved. PHP is the slowest of all, so if you loop over records in PHP it needs to be a guaranteed small set. MySQL takes a one-time hit parsing the SQL statement, but after that it can operate on the database FAR faster than PHP can. In general, let MySQL do the work, because it can do the sorting, filtering, etc. FAR faster than PHP. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which is a better design?
David:
I definitely can get the result set using one query, but what I do
with the result set has me thinking about breaking it up into two
queries.
Here's an example with a simple table:
describe collection;
+--+-+--+-
+-++
| Field| Type| Null | Key |
Default | Extra |
+--+-+--+-
+-++
| id | bigint(20) unsigned | | PRI |
NULL| auto_increment |
| receiver_id | bigint(20) unsigned | | MUL |
0 ||
| set_type_id | int(2) unsigned | | |
0 ||
| card_id | int(3) unsigned | | |
0 ||
| completed_set_id | bigint(20) unsigned | | |
0 ||
| created_on_gmt | datetime| | | -00-00
00:00:00 ||
+--+-+--+-
+-++
I want to end up with two PHP arrays. One for set_type_id = 22 and
one for set_type_id=21.
(1) one query method:
SELECT * from collection WHERE set_type_id=22 OR set_type_id=21;
...do query...
while( $row = $this-db-fetch_array_row() ){
if ($row['set_type_id'] == 21){
$array_a[] = $row;
} else {
$array_b[] = $row;
}
}
(2) two query method:
SELECT * from collection WHERE set_type_id=22;
...do query...
while( $row = $this-db-fetch_array_row() ){
$array_a[] = $row;
}
SELECT * from collection WHERE set_type_id=21;
...do query...
while( $row = $this-db-fetch_array_row() ){
$array_b[] = $row;
}
Which method is better? I still think that based on David's comments
regarding MySQL being more performative I'm leaning towards option (2).
-James
On May 10, 2007, at 12:54 PM, David T. Ashley wrote:
On 5/10/07, James Tu [EMAIL PROTECTED] wrote:
I think b/c of the way the tables are designed, I have to perform
multiple queries, unfortunately.
Hi James,
My suggestion to you would be that if you have a situation you
don't believe
you can handle in one query, post all the details to the MySQL list
and let
others take a whack at it.
I've not yet encountered a situation where the database can't be
designed
for one query results.
I also believe that MySQL has temporary table functionality:
http://www.xaprb.com/blog/2006/03/26/temporary-table-subtleties-in-
mysql/
http://www.devwebpro.com/devwebpro-39-20010817Temporary-Tables-With-
MySQL.html
I don't know how this works (I guess I should read the manual), but
I think
this would give you the ability in many cases to have MySQL (rather
than
PHP) do the heavy lifting. It will be much more efficient in MySQL
than in
PHP.
Good luck, Dave.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is a better design?
The database server and the web server are on separate machines. Table A contains a record for each user. Let's say Table B contains 'relationship' information. They can be of type 'friend' or 'family'. If a user knows another user, this relationship would be kept in this table, along with the type of relationship. Table B can get big. 10,000's or maybe 100,000's. I'm doing a query in PHP and want to end up with two arrays. One for type friend and one for type family. Which is better: (Method 1) Do ONE query for all the records that meet a certain criteria (let's say 'active'). Then use PHP to loop through the results and put each record into either the friend array or the family array. (Method 2) Do TWO queries. One just for friend. Loop through the records and put into friend array; Then do another query for family...and loop through again. Method (1) needs to evaluate an IF statement in PHP for every record. Method (2) hits the database twice, but doesn't require a PHP IF. (Should I take an extra hit on the database and use Method 2?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
I was a little to quick with the send button. Can you do a query like this: (I know that the * syntax is not correct, but is there something equivalent to it? SELECT from cars WHERE make=5 AND model=* AND body_color=7 AND tire_type = * AND hub_caps_type = 1 If you could perform a query like the one above, would MySQL still use the multi-column index that I set up? -James On Apr 24, 2007, at 4:47 PM, James Tu wrote: What do you guys think of this approach... Always query on all 5 columns...and then create a multicolumn index using all 5 columns? -James On Apr 24, 2007, at 11:42 AM, James Tu wrote: Thanks Mike. So let's say I have in index on each of the columns below...and I do a search for make=5 model=2 body_color=7 tire_type=11 hub_caps_type=1 MySQL will only pick one of them right? Let's say it picks make_index. Then what does it do? Does it scan the entire set of results returned by make=5 to match the other criteria? -James On Apr 23, 2007, at 5:49 PM, mos wrote: James, A lot depends on how many rows you are searching on. If you only have a couple thousand rows, then a table scan will still be fast. If you are searching more rows, say more than 10,000, then using the proper index will speed things up. Using a compound index is only useful if the user is searching on at least the first field of the index. For now, your best bet is to build an index on each of the commonly searched columns and MySQL will choose the best index for the search. Mike At 11:16 AM 4/23/2007, James Tu wrote: I have a table which will be searched via some of the fields in the column. An example of the list of searcheable columns: make model body_color tire_type hub_caps_type The thing is that people might do a search using one or many of the fields as criteria. For example someone might search for : body_color = 1 AND tire_type = 11 or just model = 22 I read that MySQL only uses one index when it performs a query. I did an EXPLAIN and it appears that only one of the indices is used. What is the proper way to setup indices in this case? Shoud I add an Index for each of these fields OR create a multicolumn index using all of these fields? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
Thanks Mike. So let's say I have in index on each of the columns below...and I do a search for make=5 model=2 body_color=7 tire_type=11 hub_caps_type=1 MySQL will only pick one of them right? Let's say it picks make_index. Then what does it do? Does it scan the entire set of results returned by make=5 to match the other criteria? -James On Apr 23, 2007, at 5:49 PM, mos wrote: James, A lot depends on how many rows you are searching on. If you only have a couple thousand rows, then a table scan will still be fast. If you are searching more rows, say more than 10,000, then using the proper index will speed things up. Using a compound index is only useful if the user is searching on at least the first field of the index. For now, your best bet is to build an index on each of the commonly searched columns and MySQL will choose the best index for the search. Mike At 11:16 AM 4/23/2007, James Tu wrote: I have a table which will be searched via some of the fields in the column. An example of the list of searcheable columns: make model body_color tire_type hub_caps_type The thing is that people might do a search using one or many of the fields as criteria. For example someone might search for : body_color = 1 AND tire_type = 11 or just model = 22 I read that MySQL only uses one index when it performs a query. I did an EXPLAIN and it appears that only one of the indices is used. What is the proper way to setup indices in this case? Shoud I add an Index for each of these fields OR create a multicolumn index using all of these fields? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what kind of indices to set up
What do you guys think of this approach... Always query on all 5 columns...and then create a multicolumn index using all 5 columns? -James On Apr 24, 2007, at 11:42 AM, James Tu wrote: Thanks Mike. So let's say I have in index on each of the columns below...and I do a search for make=5 model=2 body_color=7 tire_type=11 hub_caps_type=1 MySQL will only pick one of them right? Let's say it picks make_index. Then what does it do? Does it scan the entire set of results returned by make=5 to match the other criteria? -James On Apr 23, 2007, at 5:49 PM, mos wrote: James, A lot depends on how many rows you are searching on. If you only have a couple thousand rows, then a table scan will still be fast. If you are searching more rows, say more than 10,000, then using the proper index will speed things up. Using a compound index is only useful if the user is searching on at least the first field of the index. For now, your best bet is to build an index on each of the commonly searched columns and MySQL will choose the best index for the search. Mike At 11:16 AM 4/23/2007, James Tu wrote: I have a table which will be searched via some of the fields in the column. An example of the list of searcheable columns: make model body_color tire_type hub_caps_type The thing is that people might do a search using one or many of the fields as criteria. For example someone might search for : body_color = 1 AND tire_type = 11 or just model = 22 I read that MySQL only uses one index when it performs a query. I did an EXPLAIN and it appears that only one of the indices is used. What is the proper way to setup indices in this case? Shoud I add an Index for each of these fields OR create a multicolumn index using all of these fields? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what kind of indices to set up
I have a table which will be searched via some of the fields in the column. An example of the list of searcheable columns: make model body_color tire_type hub_caps_type The thing is that people might do a search using one or many of the fields as criteria. For example someone might search for : body_color = 1 AND tire_type = 11 or just model = 22 I read that MySQL only uses one index when it performs a query. I did an EXPLAIN and it appears that only one of the indices is used. What is the proper way to setup indices in this case? Shoud I add an Index for each of these fields OR create a multicolumn index using all of these fields? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Right now I'm trying to use PHP to do a binary search on the result set so I don't have to traverse the entire result set. I'm using PHP's mysql_data_seek() to move the pointer within the result set and looking at the data. What do people think of this approach? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Ok, sorry. When I described the initial scenario it wasn't exactly accurate. I want to find Joe in the list of everyone hired on the same date as Joe. So when I do my query,something to the effect of: select from users where hiring_date=$target_date ORDER BY user_uniq_id; Now the result set should be sorted by user_uniq_id. Then I do a binary search using Joe's user_uniq_id. (note user_uniq_id is an auto incremented field) That should work, no...is there a faster/better way to do this? Some people suggested creating a temporary table, but I'm just concerned that there may be memory impacts on the server if lots of people require this query. The result set could be pretty large...1000, 10,000, maybe even 100,000 if this app becomes popular. :) -James On Apr 4, 2007, at 1:21 PM, Jerry Schwartz wrote: That would only work if the result set is sorted by name. You said you wanted to sort by hiring date, that's not going to work. As for the general approach, I don't have enough experience to judge. How big would you expect the result set to be? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 1:05 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set Right now I'm trying to use PHP to do a binary search on the result set so I don't have to traverse the entire result set. I'm using PHP's mysql_data_seek() to move the pointer within the result set and looking at the data. What do people think of this approach? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
That is a nice idea, I'll have to keep it in my bag of tricks. However, I don't know if it will work b/c there are probably others that are hired on the same date... On Apr 4, 2007, at 1:51 PM, Dan Buettner wrote: James, one option would be to run a query to find the number of people in the list ahead of him, rather than determining position within the result set. As in: SELECT COUNT(*) FROM some_table WHERE state = Maine AND hire_date (SELECT hire_date FROM some_table WHERE last_name = Smith AND first_name = Joe AND state = Maine) Dan On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Let me describe the problem another way, too. It's related to creating a paging interface to view many records. I figured that people deal with paging all the time and the solution to my problem may already be out there. Typically when you access a single record via a top down method, i.e. you page through records and then you click on a specific record, you still have information that allows you to go back to the paging view. But what do people do if: (1) Somehow, while you're browsing a single record, the database is updated and records are added. The order of the record that your browsing within the entire list of records could be shifted. When you go back to page view, you actually need to figure out the position, within your overall result set, of the current record that your browsing and then you have to figure out which 'new' page the record appears on. or (2) You browse to a specific record via let's say a search, and then you want to step back to page view to see where this record lives in the overall result set. (I guess this problem is the same as (1), b/c it's all about going from a detail view to a page view and figuring out it's position relative to everything so you can form the pages accordingly) Is there a way to do this in MySQL through a query or do I need to get the ENTIRE result set into, let's say PHP and figure it out, and then do another query for just a page of results? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Thanks Maciek: The table that I'm doing this query on will be huge. It's essentially the users table for an online activity with, we hope, lots of users. :) The thing is that if I do a query for the entire result set and use PHP to figure out the position of the user and then do a query on a page of results that will contain the user, I'm still going to take a hit right? Or are you concerned about performance b/c MySQL and subqueries are really slow? -James On Mar 23, 2007, at 8:05 PM, Maciej Dobrzanski wrote: James Tu [EMAIL PROTECTED] wrote in message = news:[EMAIL PROTECTED] I want to do a query of all employees from Maine, ordered by hiring =20 date, and figure out where Joe falls in that list. (i.e. which record = number is he?) I think this can only be accomplished with a temporary table. SET @n:=3D0; SELECT t.name, t.n FROM (SELECT @n:[EMAIL PROTECTED] AS n, name FROM t WHERE state = =3D 'Maine' ORDER BY hire DESC) t WHERE t.name =3D 'Foo'; This query though may not be suitable for most situations as its = performance depends heavly on the size of the derived table.=20 Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding a record in a result set
Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up a join COUNT
Thanks! OK, here's where my understanding of MySQL and how indices work get fuzzy. In my scenario what would the difference between (I tested with different indices these and included the query times with the EXPLAIN outputs): (1) creating separate indices on entity_id and also on user_type - took 106 seconds (almost twice as long as before for some reason without these indices) EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE users ALL user_idx,entity_idx NULL NULL NULL 750106 Using where; Using temporary; Using filesort 1 SIMPLE geo_entitieseq_ref PRIMARY PRIMARY 4 users.entity_id 1 vs (2) creating a multicolumn index with entity_id and user_type - (as Filip has suggested) - took only 0.4 seconds, wow what a difference !!! EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geo_entities ALL PRIMARY NULL NULL NULL 238 Using temporary; Using filesort 1 SIMPLE users ref geo_idx geo_idx 7 geo_entities.id,const 4202 Using where; Using index vs (3) with all three indices in place - took 86 seconds. EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geo_entities ALL PRIMARY NULL NULL NULL 238 Using temporary; Using filesort 1 SIMPLE users ref user_type_idx,entity_idx,geo_idx geo_idx 7 geo_entities.id,const 4202 Using where; Using index -James On Jan 25, 2007, at 8:35 AM, Filip Krejc(í [EMAIL PROTECTED] Filip Krejc wrote: Hi, try CREATE INDEX geo_idx ON users(entity_id, user_type); Filip Alex Arul napsal(a): and also an index on users.entity_id (will help the join) should solve your problem. Thanks Alex On 1/24/07, Brent Baisley [EMAIL PROTECTED] wrote: You should create indexes on the fields you search on most. In this case, you are searching on the user_type field, so create an index on that field. Otherwise you need to scan the entire table to find out which users are of the type you are searching for. - Original Message - From: James Tu [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 12:04 PM Subject: speeding up a join COUNT I'm performance testing my 'users' table. It currently has roughly 1M user records. The 'geo_entities' table has ~ 250 records. Here's my query. SELECT users.entity_id, geo_entities.entity_name, geo_entities.short_code, COUNT ( users.entity_id) FROM users, geo_entities WHERE users.user_type = 'user' AND users.entity_id = geo_entities.id GROUP BY entity_id LIMIT 0 , 30 It took 51 seconds to execute. Both tables only have an index on their unique record id. Is there a way to speed up this up? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Filip Krejci [EMAIL PROTECTED] Why use Windows, since there is a door?
speeding up a join COUNT
I'm performance testing my 'users' table. It currently has roughly 1M user records. The 'geo_entities' table has ~ 250 records. Here's my query. SELECT users.entity_id, geo_entities.entity_name, geo_entities.short_code, COUNT( users.entity_id ) FROM users, geo_entities WHERE users.user_type = 'user' AND users.entity_id = geo_entities.id GROUP BY entity_id LIMIT 0 , 30 It took 51 seconds to execute. Both tables only have an index on their unique record id. Is there a way to speed up this up? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy errors osx
The first time I tried to run the mysqlhotcopy script, I got an error
that indicated that Perl couldn't find the DBI module.
So, I found a site (http://www.quicomm.com/apm_dbddbi.htm) that
stepped me through making and installing
MySQL DBD and the DBI modules(? don't know if I'm using the right
terminology here)
However, during the 'make' step of the DBD module I got a warning:
prompt$ sudo Perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite Net::MySQL 0.08 not found.
Writing Makefile for DBD::mysqlPP
So I decided to try to 'make install' anyway and I got...
$ sudo make install
Installing /Library/Perl/5.8.6/DBD/mysqlPP.pm
Installing /usr/local/man/man3/DBD::mysqlPP.3pm
Writing /Library/Perl/5.8.6/darwin-thread-multi-2level/auto/DBD/
mysqlPP/.packlist
Appending installation info to //System/Library/Perl/5.8.6/darwin-
thread-multi-2level/perllocal.pod
trying to run mysqlhotcopy again, I got...
$ mysqlhotcopy --dryrun imaginon
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC
contains: /System/Library/Perl/5.8.6/darwin-thread-multi-2level /
System/Library/Perl/5.8.6 /Library/Perl/5.8.6/darwin-thread-
multi-2level /Library/Perl/5.8.6 /Library/Perl /Network/Library/Perl/
5.8.6/darwin-thread-multi-2level /Network/Library/Perl/5.8.6 /Network/
Library/Perl /System/Library/Perl/Extras/5.8.6/darwin-thread-
multi-2level /System/Library/Perl/Extras/5.8.6 /Library/Perl/5.8.1 .)
at (eval 7) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Proxy, Sponge, mysqlPP.
at /usr/local/mysql/bin/mysqlhotcopy line 178
line 178 of the script looks like:
my $dbh = DBI-connect(dbi:mysql:
$dsn;mysql_read_default_group=mysqlhotcopy,
$opt{user}, $opt{password},
should I change the dbi:mysql part to dbi:mysqlPP ???
or should I fix the earlier warning about Net:MySQL 0.08 not found
first. Do I just install the Net package?
...OK
I actually downloaded and installed Net:MySQL...
sudo Perl Makefile.PL
sudo make
sudo make test
sudo make install
...all went ok
then I did the same steps for DBD
...all went ok and no warnings...
But I'm still getting the same 'line 178' error.
What am I not doing correctly?
Thanks.
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to backup a 24/7 database
We're working on a site that will most likely be up 24 hours a day. What is the best backup strategy for the database? The client will be using hosting services, but they haven't' picked anyone yet. I've been playing around with mysqlimport (pretty straightforward) and mysqlhotcopy (haven't been able to run it...don't have the appropriate Perl modules.) We'll probably just take a daily 'snapshot' of the database. What is the best way to do this? Am I safe with writing my own shell scripts that essentially just calls mysqlimport? Does that handle a live database w/o any issues? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL to log and report transactions
Hi: I'm going use MySQL to log transactions so that I can report on them later. Ex: CREATE TABLE statistics ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , user_id BIGINT UNSIGNED , entity_id INT UNSIGNED , transaction_type INTEGER(2) UNSIGNED , datetime_logged DATETIME , datetime_logged_gmt DATETIME , PRIMARY KEY (id) ); What are the queries that would get me the data organized by: Months Weeks Days Day of Week? I guess I can add the fields: month day year hour dayofyear and that would make reporting a lot easier! How do people typically go about doing this without breaking down the datetime? -James
Database design question
I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Wow, I didn't know that can happen. I'll definitely take that into consideration. Thanks Brent. On Aug 7, 2006, at 4:26 PM, Brent Baisley wrote: If you're looking to be put at ease, I've got a table with 250+ million records, but I've heard of people with larger tables than that on this list. You might want to also looking into using a compound primary key, meaning userid+messageid. Something like this: CREATE TABLE `message` ( `userid` int unsigned NOT NULL default '', `messageid` int unsigned NOT NULL auto_increment, `message` text, ... PRIMARY KEY (`userid`,`messageid`) ) What that does is give each user their own incrementing message id. Then you can do things like allow users to enter a message id directly with a number that would be easy for them to remember. Just an idea. - Original Message - From: James Tu [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, August 07, 2006 4:11 PM Subject: Re: Database design question Thanks everyone. Now I feel confident that one table will be fine (Tripp's stat of 30 million records put me at ease :) ). Cheers, -James On Aug 7, 2006, at 4:08 PM, John Meyer wrote: One table, USERS Another table MESSAGES With a foreign key referencing users. Maybe a second foreign key referencing the destinating user as well. -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Monday, August 07, 2006 1:56 PM To: mysql@lists.mysql.com Subject: Database design question I want to design a database for lots of users. Each user will be managing their own messages. Does it make sense to create a table for each user after they've registered? Or should I just create one MESSAGES table and store messages there keyed off of their user_id? If I create a table for each user (I can potentially have hundreds of thousands of users), will MySQL be able to handle this? If I just have one table, I could potentially have millions of records in one table. Will MySQL be able to handle this? My gut feel is that MySQL will be fine with one table and millions of records. Thanks. -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are these db stats normal...part 2
I've also checked the error log and there isn't anything that indicates where these aborted_client connections are coming from. I checked today and I have 100 more of these. -James
Are these db stats normal?
I used phpMyAdmin to look at the stats for my MySQL server. here's what they show... http://www.2-bit-toys.com/db_info/server_status.html What concerns me mainly are the stats at the top-right...'Failed attempts' and 'Aborted.' When would these situations occur? Is it normal to see these? I'm using PHP's mysql_pconnect for all my scripts. Could this be causing the failed/aborted attempts? What is not clear in the documentation is whether mysql_pconnect will open another connection if the current one is in use. -James
Are these db stats normal...part 2
I just issued a show status query and the numbers are what MySQL returns. Strange. Failed attempts (aka aborted_clients) : 8154 Aborted (aka Aborted_connects): 319 Total (aka Connections) : 4626 So phpMyAdmin is basing the % calculations on these numbers, that's why we see the weird percentage. The aborted_clients and the aborted_connects concern me, but the applications seem to be working fine! I found the following: http://dev.mysql.com/doc/refman/4.1/en/communication-errors.html My scripts are using mysql_pconnect(), so I don't call mysql_close() (they list not calling mysql_close() as a possible cause for seeing Aborted_clients increment.) They also said...The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld. See Section A.2.9, Packet too large. I haven't tested this but we never encountered this when we were testing with large data in development. I'm looking at the data heavy tables in production and they average 140K per row...which is not even close to the 1MB max_allowed_packet default limit. Any ideas? Anybody else encounter this? -James
Backup and Maintenance Strategies
What have people done in the past regarding backup strategies? Is it adequate enough to rely on filesystem backups for mysql? Basically such that we can restore MySQL to the last filesystem backup. Is there a reason not to do this? I don't have any mission critical data and data that is lost since the last backup is acceptable. -James
Re: Fw: Inner workings of a JOIN
Thank you for all of your answers and suggestions. I feel a lot more confident finishing my table designs -James
Query performance...two table design options
Hi: Let's say I want to store the following information. Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT I would be basing my queries on all columns _except_ the Data column. I.e. I would be using WHERE's with all except the Data column. My question is...which design would perform better? (Design A) Put all in one table...index all the columns that I will use WHERE with. -TABLE_ALL- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data - TEXT Indices - Unique ID, First Name, Last Name, Age, Date, Activity SELECT First_Name, Last_Name, Data FROM TABLE_ALL WHERE Activity = 'draw' AND Age 24; (Design B) Put the Data in its own separate table. -TABLE_A- Unique ID - INT(10) autoincrement First Name - VARCHAR (25) Last Name - VARCHAR (25) Age - INT(3) Date - DATETIME Activity - VARCHAR(100) Data_ID - INT(10) Indices - Unique ID, First Name, Last Name, Age, Date, Activity -TABLE_B- Data_ID - INT(10) Data - TEXT Index - Data_ID SELECT TABLE_A.First_Name, TABLE_A.Last_Name, TABLE_B.Data FROM TABLE_A, TABLE_B WHERE Activity = 'draw' AND Age 24 AND TABLE_A.Data_ID = TABLE_B.Data_ID; (Aside: Would this query give me the same results as the above query?) -James
Inner workings of a JOIN
What does MySQL do internally when you perform a LEFT JOIN? Let's say you have two tables: Table A has 1,000,000 rows Table B has 5,000 rows When you perform the following LEFT JOIN: Select A.*, B.* FROM A, B WHERE A.lastname = 'doe' AND A.id http://A.id = B.id http://B.id What does MySQL do internally? Does it first create some sort of CROSS JOIN with the two tables (resulting in a 5,000,000,000 row table) and then finding the matching rows based on the WHERE clause? -James
Upgrading from 4.0 to 4.1
I had to migrate from 4.0.11 to 4.1.12. I saved the data directory from the older version. I also exported all the information using phpMyAdmin's export tool. I have one huge .sql file. When I upgraded to 4.1.12, I followed instructions to copy the contents of the old data directory to the new one. This didn't work...MySQL had trouble starting up. So, I restored the new data folder, and ran the .sql file to recreate all the table and to insert all the new records. This works fine. What is the easiest way to upgrade and migrate the data? Did I do something wrong by just copying the contents of the data directory? Should that method work? -James
database design question
I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
