Re: Postal code searching
Thanks for your very detailed response Mark. Most helpful. On Wed, Apr 25, 2012 at 10:14 AM, Mark Goodge m...@good-stuff.co.uk wrote: On 24/04/2012 17:24, Tompkins Neil wrote: How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code No, you don't. In this example you have W1U as one outbound code and W13 as the other. W1U postcodes are not a subset of W1 postcodes, any more than IP27 postcodes are a subset of IP2 postcodes. The fact that in W1U the district segment is in the form of NA rather than NN doesn't change the fact that it's an indivisible two-character code. So I think the first question has to be, why do you want to get W1 as a particular substring from the postcode W1U 8JE? British postcodes have a structure which is easy for humans to understand, although (unfortunately) rather hard to parse automatically. Essentially, every full postcode contains four elements: Area code: one or two alpha characters, either A or AA District code: one or two alphanumeric characters the first of which is always numeric, either N, NN or NA Sector code: single numeric character, always N Walk code: two alpha characters, always AA It's customary, but not part of the formal specification, to insert whitespace between the District and Sector codes. So, given the postcode WC1H 8EJ, we have: Area: WC District: 1H Sector: 8 Walk: EJ Taken together, the first two sections form the outbound part of the postcode, and the second two form the inbound. (That is, the first two identify the destination sorting depot that the originating depot will send the post to, and the second two are used by the destination depot to make the actual delivery). The reason for mentioning this is that postcodes, having a wide range of possible formats, are not easy to handle with simple substring searches if you're trying to extract outbound codes from a full postcode. It can be done with regular expressions, but you have to be wary of assuming that the space between District and Sector will always be present as, particularly if you're getting data from user input, it might not be. In my own experience (which is quite extensive, as I've done a lot of work with systems, such as online retail, which use postcodes as a key part of the data), I've always found it simpler to pre-process the postcodes prior to inserting them into the database in order to ensure they have a consistent format (eg, inserting a space if none exists). That then makes it easy to select an outbound code, as you can use the space as a boundary. But if you want to be able to go further up the tree and select area codes (eg, distinguishing between EC, WC and W) then it's harder, as you have to account for the fact that some are two characters and some are only one. You can do it with a regular expression, taking everything prior to the first digit, but it's a lot easier in this case to extract the area code prior to inserting the data into the database and store the area code in a separate column. Mark -- Sent from my ZX Spectrum HD http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Postal code searching
Hi I've a number of different postal codes in a system for example WC1B 5JA WC1H 8EJ W1J 7BX W1H 7DL NW1 1NY I can use like statements for example SELECT * FROM postal_codes WHERE zip LIKE 'W1%' giving me W1J 7BX W1H 7DL In addition I have a number of abbreviated postal codes like W1 WC1 WC2 NW1 Now, if I know the postal code W1J 7BX what is the best way using a MySQL query to get the abbreviated postal codes W1. Same if I have the postal code WC1H 8EJ, how do I get the abbreviated postal codes WC1 Can I use any matching patterns ? Thanks, Neil
Re: Postal code searching
How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code On Tue, Apr 24, 2012 at 5:18 PM, Gary Smith li...@l33t-d00d.co.uk wrote: On 24/04/2012 17:16, Gary Smith wrote: http://dev.mysql.com/doc/**refman/5.0/en/string-** comparison-functions.htmlhttp://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html Specifically, replace % with _ as this means match one character not match any number of characters. So, you can do: like W1 % like W1_ % etc. Oh, and you can also get really dirty and start using string functions like left() and so on. Gary
JOIN giving duplicate records
Hi Before sending the table definition, and queries etc, can anyone advise why my query with four INNER JOIN might be give me back duplicate results e.g 100,UK,12121 100,UK,12121 Basically the query the statement AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7) and if I add additional IDs like AND (hotel_facilities.hotelfacilitytype_id = 47 OR hotel_facilities.hotelfacilitytype_id = 7 OR hotel_facilities.hotelfacilitytype_id = 8), it would return three records like below when I'm expecting one record. 100,UK,12121 100,UK,12121 100,UK,12121 However in my SELECT statement if I use SELECT DISTINCT (field_name), I only get back one record. Thanks, Neil
Design advice for hotel availability program
Hi, I'm in the process of starting to design a hotel booking system that will eventually consist of over 10,000 hotels all with different room types, rates and availability for different dates. My question is does anyone have any experience with regards the best way to store the daily rates. Am I best using the following pattern (1) Default rates/rooms stored in a generic table (2) Any exceptions/changes/closed days to the daily rates are store in a separate table. (3) Any special offer exceptions are stored as a rule All, should I consider that for any hotel, for any room, for any day I have a record in a huge single table ??? Thanks for any help and input. Best, Neil
Chinese characters not displaying in Workbench latest version
Does anyone know why Chinese characters are not displaying correctly in a replicated database on a slave machine ? I'm just getting square boxes. Thanks Neil
Re: Fwd: Large insert query gives MySQL server gone away
Do you have any recommendations as to what size this should be increased to ? On Tue, Oct 11, 2011 at 12:23 AM, Angela liu yyll2...@yahoo.com wrote: Yeah, I think adjusting max packet size may be helpful, remember change that value in both server and client -- *From:* Johnny Withers joh...@pixelated.net *To:* Neil Tompkins neil.tompk...@googlemail.com *Cc:* [MySQL] mysql@lists.mysql.com *Sent:* Monday, October 10, 2011 4:13 PM *Subject:* Re: Fwd: Large insert query gives MySQL server gone away Max packet size? On Oct 10, 2011 6:12 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: As per the subject we've a large insert query that gives up the error MySQL server has gone away when we try to execute it. Any ideas why ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
Re: Large insert query gives MySQL server gone away
But how do I calculate such size ? On Wed, Oct 12, 2011 at 2:38 PM, Reindl Harald h.rei...@thelounge.netwrote: as big as you largest query Am 12.10.2011 15:30, schrieb Tompkins Neil: Do you have any recommendations as to what size this should be increased to ? On Tue, Oct 11, 2011 at 12:23 AM, Angela liu yyll2...@yahoo.com wrote: Yeah, I think adjusting max packet size may be helpful, remember change that value in both server and client -- *From:* Johnny Withers joh...@pixelated.net *To:* Neil Tompkins neil.tompk...@googlemail.com *Cc:* [MySQL] mysql@lists.mysql.com *Sent:* Monday, October 10, 2011 4:13 PM *Subject:* Re: Fwd: Large insert query gives MySQL server gone away Max packet size? On Oct 10, 2011 6:12 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: As per the subject we've a large insert query that gives up the error MySQL server has gone away when we try to execute it. Any ideas why ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: Index question
Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Rik Wasmus r...@grib.nl Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. I'm trying to think of a scenario where index(a) would be beneficial in the presence of index(a,b). If both are available, and all else being equal, it's likely that the parser will pick the simplest index; but I can't see it having a major impact. Any full prefix of a combined index may be used; so afaik a separate index on any full prefix is a waste of diskspace and cycles. The net conclusion, Neil, is that you actually have to know what you're doing :-) Take the time to read the online documentation on mysql.com, it's pretty good. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: MySQL Indexes
Is it normal practice for a heavily queried MYSQL tables to have a index file bigger than the data file ? On Fri, Oct 7, 2011 at 12:22 AM, Michael Dykman mdyk...@gmail.com wrote: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
MySQL Indexes
Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil
Re: Slow query - please help
Hi
Here is the response :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ALL', 'IDX_language_code', NULL,
NULL, NULL, '1207', '99.75', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ALL', 'IDX_language_code', NULL, NULL,
NULL, '163102', '100.00', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_enabled,IDX_language_code', 'IDX_enabled', '1', 'const',
'267', '100.00', 'Using index condition; Using where'
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`maxrate` double DEFAULT NULL,
`minrate` double DEFAULT NULL,
`original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nr_rooms` int(11) DEFAULT NULL,
`preferred` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`review_nr` int(11) DEFAULT NULL,
`review_score` double DEFAULT NULL,
`zip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_from` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`checkin_to` varchar(15) COLLATE
Re: Slow query - please help
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`maxrate` double DEFAULT NULL,
`minrate` double DEFAULT NULL,
`original_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`nr_rooms` int(11) DEFAULT NULL,
`preferred` int(11) DEFAULT NULL,
`ranking` int(11) DEFAULT NULL,
`review_nr` int(11) DEFAULT NULL
Fwd: Slow query - please help
Following my mail below, if anyone can help optimise the query further that
would be a great help.
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 5, 2011 at 9:48 AM
Subject: Re: Slow query - please help
To: Johnny Withers joh...@pixelated.net
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition; Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`class_is_estimated` tinyint(4) DEFAULT NULL,
`class` tinyint(4) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`currencycode` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`district` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`hoteltype_id` int(11) DEFAULT NULL,
`is_closed` tinyint(4) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double
Re: Slow query - please help
I already have a FULLTEXT index on cities.name ? Do I still need to add a
normal index ?
On Wed, Oct 5, 2011 at 1:34 PM, Johnny Withers joh...@pixelated.net wrote:
Try adding an index on cities.name, it may prevent the file sort. What was
the original query time and what is it now?
Sent from my iPad
On Oct 5, 2011, at 4:01 AM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
Following my mail below, if anyone can help optimise the query further
that
would be a great help.
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 5, 2011 at 9:48 AM
Subject: Re: Slow query - please help
To: Johnny Withers joh...@pixelated.net
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
I just revised my query and now get the following output :
'1', 'PRIMARY', 'cities', 'ref', 'IDX_country_code,IDX_language_code',
'IDX_country_code', '7', 'const', '2088', '100.00', 'Using index
condition;
Using where; Using filesort'
'4', 'DEPENDENT SUBQUERY', 'districts', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'121', '100.00', 'Using index condition; Using where'
'3', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'IDX_country_code,IDX_language_code', 'IDX_country_code', '7', 'const',
'9982', '100.00', 'Using index condition; Using where'
'2', 'DEPENDENT SUBQUERY', 'hotels', 'ref',
'UNQ_folder_url,IDX_country_code,IDX_enabled,IDX_language_code',
'IDX_enabled', '1', 'const', '267', '100.00', 'Using index condition;
Using
where'
After doing this the query speed is acceptable.
Thanks
Neil
On Wed, Oct 5, 2011 at 3:12 AM, Johnny Withers joh...@pixelated.net
wrote:
Can you post the explain extended output of your query?
Sent from my iPad
On Oct 4, 2011, at 2:45 PM, Neil Tompkins neil.tompk...@googlemail.com
wrote:
Can anyone help me ?
Begin forwarded message:
From: Tompkins Neil neil.tompk...@googlemail.com
Date: 30 September 2011 20:23:47 GMT+01:00
To: mark carson mcar...@pixie.co.za
Cc: [MySQL] mysql@lists.mysql.com
Subject: Re: Slow query - please help
I downloaded version mysql-5.6.2-m5-win32.msi and he table definitions
are below, let me know if you need any more information.
CREATE TABLE `districts` (
`district_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`city_id` int(11) DEFAULT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`district_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `cities` (
`city_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL,
`country_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`nr_hotels` int(11) DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_keywords` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`meta_description` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`link_text` varchar(155) COLLATE utf8_unicode_ci DEFAULT NULL,
`folder_url` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`enabled` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
`last_changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`,`language_code`),
UNIQUE KEY `UNQ_folder_url` (`folder_url`),
KEY `IDX_country_code` (`country_code`),
KEY `IDX_enabled` (`enabled`),
KEY `IDX_folder_url` (`folder_url`),
KEY `IDX_language_code` (`language_code`),
KEY `IDX_latitude` (`latitude`),
KEY `IDX_longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `hotels` (
`hotel_id` int(11) NOT NULL,
`language_code` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city_id` int(11) DEFAULT NULL
Slow query - please help
Hi I've the following query : SELECT city_id, name, meta_title, meta_description, meta_keywords, country_code, link_text, folder_url, enabled, last_changed, nr_hotels, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.enabled = 'Y' AND hotels.folder_url IS NOT NULL AND hotels.language_code = 'en') AS hotel_count, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.language_code = 'en') AS available_hotel_count, (SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_id = cities.city_id AND districts.language_code = 'en' AND districts.country_code = 'gb') AS district_count FROM cities WHERE language_code = 'en' AND country_code = 'gb' ORDER BY cities.name ASC , cities.city_id ASC Previously the table format was Innodb with foreign keys and the query was pretty much instant. Now I've changed the table format to MyISAM and obviously removed the foreign keys and the query takes forever to execute using the same data. Can anyone help and tell me where I've gone wrong. Thanks Neil
Re: Slow query - please help
a response Mark On 2011/09/30 17:49, Tompkins Neil wrote: Hi I've the following query : SELECT city_id, name, meta_title, meta_description, meta_keywords, country_code, link_text, folder_url, enabled, last_changed, nr_hotels, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.enabled = 'Y' AND hotels.folder_url IS NOT NULL AND hotels.language_code = 'en') AS hotel_count, (SELECT COUNT(hotels.hotel_id) FROM hotels WHERE hotels.city_id = cities.city_id AND hotels.country_code = 'gb' AND hotels.language_code = 'en') AS available_hotel_count, (SELECT COUNT(districts.city_id) FROM districts WHERE districts.city_id = cities.city_id AND districts.language_code = 'en' AND districts.country_code = 'gb') AS district_count FROM cities WHERE language_code = 'en' AND country_code = 'gb' ORDER BY cities.name ASC , cities.city_id ASC Previously the table format was Innodb with foreign keys and the query was pretty much instant. Now I've changed the table format to MyISAM and obviously removed the foreign keys and the query takes forever to execute using the same data. Can anyone help and tell me where I've gone wrong. Thanks Neil -- Mark Carson Managing Integrated Product Intelligence CC (CK95/35630/23) EMail : mcar...@ipi.co.za/ (secondary:mcar...@pixie.co.za) Physical Address : 34 Spanish Galliard, Mooikloof, Pretoria, South Africa snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Tel. +27 12 996 1193/1815 Fax : +27 86 672 7012 Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT.
replication between two tables in same database
Hi I've a Innodb and MyISAM table in the SAME database that I wish to replicate the data between the two because I need to use FULLTEXT searching on the MyISAM table. Is this possible ? If so how do I do it. Thanks Neil
Re: replication between two tables in same database
Yes, unless I can set-up some sort of replication between the two tables. On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.netwrote: please do NOT post off-list! so your only workaround is like '%whatever%' currently Am 29.09.2011 19:04, schrieb Tompkins Neil: The reason I'm using Innodb is because the usage of foreign keys in short however, we need a couple of the tables to support FULLTEXT searching which Innodb does not support. On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.netwrote: Am 29.09.2011 18:56, schrieb Tompkins Neil: Hi I've a Innodb and MyISAM table in the SAME database that I wish to replicate the data between the two because I need to use FULLTEXT searching on the MyISAM table. Is this possible ? If so how do I do it. in short: no in longer: why in the world are you using the wrong engine if you need fulltext-search? mysql 5.6 semmes to start support this in innodb, but currently not replication is based on binlogs and contains database/table so there is no dirty trick do this on one server except let run a script and copy the table-contents per cronjob -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
Re: replication between two tables in same database
We've succesfully used FULLTEXT searching on another application that does not need Innodb tables. In addition for the FULLTEXT searching we use things like IN BOOLEAN MODE as well - so for our project Like %% is not enough. On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald h.rei...@thelounge.netwrote: you do not need any replication or myisam for select * from table where field like '%input%'; for most workloads this is enough and you have not the problem with stop-words, minimum input length and so on Am 29.09.2011 19:07, schrieb Tompkins Neil: Yes, unless I can set-up some sort of replication between the two tables. On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.net wrote: please do NOT post off-list! so your only workaround is like '%whatever%' currently Am 29.09.2011 19:04, schrieb Tompkins Neil: The reason I'm using Innodb is because the usage of foreign keys in short however, we need a couple of the tables to support FULLTEXT searching which Innodb does not support. On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 29.09.2011 18:56, schrieb Tompkins Neil: Hi I've a Innodb and MyISAM table in the SAME database that I wish to replicate the data between the two because I need to use FULLTEXT searching on the MyISAM table. Is this possible ? If so how do I do it. in short: no in longer: why in the world are you using the wrong engine if you need fulltext-search? mysql 5.6 semmes to start support this in innodb, but currently not replication is based on binlogs and contains database/table so there is no dirty trick do this on one server except let run a script and copy the table-contents per cronjob -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm
MySQL 5.6
Hi Does anyone know when the production release of MySQL 5.6 will be out ? Thanks Neil
Re: replication between two tables in same database
I've now just upgraded to MySQL 5.6 on our test server. Just trying to find a date as to when it will go into production On Thu, Sep 29, 2011 at 8:10 PM, mos mo...@fastmail.fm wrote: Derek is right. The Sphynx search engine is much faster than MyISAM's full text search engine. It will work on InnoDb tables so you don't have to export the data to MyISAM. Mike At 01:43 PM 9/29/2011, you wrote: But I could create an additional myisam table to overcome my problem providing I can get the data to synchronise between the two tables On 29 Sep 2011, at 18:16, Reindl Harald h.rei...@thelounge.net wrote: so mysql is currently the wrong database for your project sad but true, you can not have fulltext-search and innodb this time Am 29.09.2011 19:15, schrieb Tompkins Neil: We've succesfully used FULLTEXT searching on another application that does not need Innodb tables. In addition for the FULLTEXT searching we use things like IN BOOLEAN MODE as well - so for our project Like %% is not enough. On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald h.rei...@thelounge.netmailto: h.rei...@thelounge.net** wrote: you do not need any replication or myisam for select * from table where field like '%input%'; for most workloads this is enough and you have not the problem with stop-words, minimum input length and so on Am 29.09.2011 19:07, schrieb Tompkins Neil: Yes, unless I can set-up some sort of replication between the two tables. On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net**wrote: please do NOT post off-list! so your only workaround is like '%whatever%' currently Am 29.09.2011 19:04, schrieb Tompkins Neil: The reason I'm using Innodb is because the usage of foreign keys in short however, we need a couple of the tables to support FULLTEXT searching which Innodb does not support. On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net**wrote: Am 29.09.2011 18:56, schrieb Tompkins Neil: Hi I've a Innodb and MyISAM table in the SAME database that I wish to replicate the data between the two because I need to use FULLTEXT searching on the MyISAM table. Is this possible ? If so how do I do it. in short: no in longer: why in the world are you using the wrong engine if you need fulltext-search? mysql 5.6 semmes to start support this in innodb, but currently not replication is based on binlogs and contains database/table so there is no dirty trick do this on one server except let run a script and copy the table-contents per cronjob -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%**203999%2033, m: +43 (676) 40 221 40 tel:%2B43%20%28676%29%2040%**20221%2040 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%**203999%2033, m: +43 (676) 40 221 40 tel:%2B43%20%28676%29%2040%**20221%2040 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?**unsub=mo...@fastmail.fmhttp://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?**unsub=neil.tompkins@** googlemail.comhttp://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Query help
Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil
Re: Query help
Thanks for the response. This is what I was after. Although, I am looking to find out the email addresses used to login from the same IP ? On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, March 02, 2011 6:00 AM To: [MySQL] Subject: Query help Hi I've the following basic table login_id email_address ip_address I want to extract all records from this table in which a user has used the same IP address but different email address to login ? Thanks, Neil [JS] I haven't looked at my code lately, but I'm pretty sure that SELECT ip_address FROM basic_table GROUP BY ip_address HAVING COUNT(*) 1; is what you want. You don't need to group on login_id. And, as Claudio said, SELECT ip_address, GROUP_CONCAT(login_id, ', ') AS list_of_login_ids will give you the IP addresses as well. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com
Re: SELECT Help
Hi Travis, That query kind of gives me the desired result. However, if is showing me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6, 2010-12-20 22:17:13, which is when they changed teams. Any thoughts ? Cheers Neil On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard travis_...@hotmail.com wrote: Something like this might help you find all of the times where your user_id switched to a different team_id: select team_id, user_id, min(last_changed) from (select home_team_id as team_id, home_user_id as user_id, last_changed from data union all select away_team_id as team_id, away_user_id as user_id, last_changed from data) s1 where s1.user_id = 3 group by team_id, user_id; -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, February 03, 2011 6:34 AM To: [MySQL] Subject: SELECT Help Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil
Re: Unknown column 'users.users_id' in 'where clause'
Thanks, but how can I pass the current users value from the other query ? On Thu, Feb 3, 2011 at 4:22 PM, Simcha Younger sim...@syounger.com wrote: On Thu, 3 Feb 2011 13:55:36 + Tompkins Neil neil.tompk...@googlemail.com wrote: SELECT DISTINCT(away_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.away_users_id = *users.users_id*) Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause' for the part of the statement that I've marked as *bold* and how I can over come this problem ? You have to add the users table to the subquery. Your subquery only has the fixtures_results table in the 'from' clause, and the subquery is not aware of the tables you use in the outer query. -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
SELECT Help
Hi, I've the following list of sample data, and need a SELECT statement to help me identify the point at which I've highlighted the data : Season, Competition, home_team_id, away_team_id, home_user_id, away_user_id, last_changed 1, 18, 11, 23, 3, 2010-11-14 17:18:17 1, 11, 8, 3, 82, 2010-11-14 18:37:44 1, 20, 11, 69, 3, 2010-11-17 23:07:49 1, 1, 11, 4, 3, 2010-11-18 19:00:26 1, 11, 1, 3, 4, 2010-11-18 19:00:42 1, 12, 11, 5, 3, 2010-11-19 22:49:49 1, 11, 14, 3, 19, 2010-11-23 21:38:19 1, 3, 11, 15, 3, 2010-11-25 22:08:23 1, 7, 11, 66, 3, 2010-11-28 02:38:15 2, 73, 60, 137, 3, 2010-12-08 00:22:30 2, 60, 73, 3, 137, 2010-12-08 00:22:35 2, 60, 37, 3, 112, 2010-12-09 20:05:44 2, 60, 65, 3, 158, 2010-12-12 21:45:14 2, 72, 60, 141, 3, 2010-12-13 15:38:25 2, 60, 68, 3, 87, 2010-12-13 16:08:08 2, 60, 45, 3, 8, 2010-12-13 22:34:40 2, 66, 60, 140, 3, 2010-12-14 22:10:42 2, 60, 71, 3, 142, 2010-12-16 19:48:46 2, 60, 64, 3, 30, 2010-12-19 16:41:21 2, 76, 60, 17, 3, 2010-12-19 19:17:04 2, 60, 76, 3, 17, 2010-12-20 00:40:56 *2, 11, 10, 3, 6, 2010-12-20 22:17:13* 2, 13, 11, 104, 3, 2010-12-21 00:36:37 2, 6, 11, 168, 3, 2010-12-29 20:20:52 2, 11, 18, 3, 97, 2010-12-29 20:41:07 2, 20, 11, 5, 3, 2010-12-30 21:24:58 2, 15, 11, 163, 3, 2010-12-30 21:46:39 2, 13, 11, 12, 3, 2010-12-30 22:33:15 Basically, I need to find the point in which the user for either home_user_id or away_user_id (in this instance 3) changed teams for home_team_id or away_team_id - if you understand what I mean ? Any ideas on how I can achieve this using MySQL ? Cheers Neil
Unknown column 'users.users_id' in 'where clause'
Hi, I've the following SELECT statement SELECT users.gamer_tag, UNIX_TIMESTAMP(users.created_on) AS time_registered, (SELECT fixtures_results.last_changed FROM fixtures_results WHERE (home_users_id = users.users_id AND home_teams_id = users_teams.teams_id) OR (away_users_id = users.users_id AND away_teams_id = users_teams.teams_id) AND fixtures_results.status = 'approved' ORDER BY fixtures_results.last_changed ASC LIMIT 1) AS time_at_team, (SELECT COUNT(DISTINCT(teams_id)) FROM (SELECT DISTINCT(home_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.home_users_id = *users.users_id*) AND fixtures_results.status = 'approved' UNION ALL SELECT DISTINCT(away_teams_id) AS teams_id FROM fixtures_results WHERE (fixtures_results.away_users_id = *users.users_id*) AND fixtures_results.status = 'approved') s1) AS number_teams FROM users INNER JOIN users_teams ON users.users_id = users_teams.users_id WHERE UNIX_TIMESTAMP(users.created_on) 0 AND (SELECT fixtures_results.last_changed FROM fixtures_results WHERE (home_users_id = users.users_id AND home_teams_id = users_teams.teams_id) OR (away_users_id = users.users_id AND away_teams_id = users_teams.teams_id) AND fixtures_results.status = 'approved' ORDER BY fixtures_results.last_changed ASC LIMIT 1) '' ORDER BY time_at_team ASC, time_registered ASC Any ideas why I'm getting Unknown column 'users.users_id' in 'where clause' for the part of the statement that I've marked as *bold* and how I can over come this problem ? Cheers Neil
Error Unknown column in 'where clause'
Hi I've the following query : SELECT players_bids.players_bids_id, players_bids.players_id, players_bids.bid_date, players_bids.bid_type, players_bids.bid_value, (SELECT SUM(IF(home_users_id = players_bids.users_id_from,home_manager_points,away_manager_points)) FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from OR away_users_id = players_bids.users_id_from) AND worlds_id = 1) AS manager_points, players_bids.users_id_from, (SELECT ROUND((SUM(won_home)+SUM(won_away))/COUNT(*)*100,0) FROM (SELECT IF(home_goals away_goals, 1, 0) AS won_home ,0 AS won_away FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from) AND status = 'approved' UNION ALL SELECT 0 AS won_home ,IF(away_goals home_goals, 1, 0) as won_away FROM fixtures_results WHERE away_users_id = players_bids.users_id_from AND status = 'approved') s1) AS wins, players_bids.users_id_to, players_bids.worlds_id, players_bids.seasons_id, players_master.first_name, players_master.second_name, players_master.known_as, players_master.estimated_value, players_master.rating, players_master.positions_id, players.games_played, players_bids.teams_id_from, players_bids.teams_id_to, teams_master_from.team_name AS team_name_from, teams_master_to.team_name AS team_name_to FROM players_bids INNER JOIN players ON players_bids.players_id = players.players_id INNER JOIN players_master ON players_bids.players_id = players_master.players_id INNER JOIN teams_master teams_master_from ON players_bids.teams_id_from = teams_master_from.teams_id INNER JOIN teams_master teams_master_to ON players_bids.teams_id_to = teams_master_to.teams_id WHERE players_bids.bid_status = 'accepted' AND players_bids.players_id = 279 AND players_bids.worlds_id = 1 ORDER BY players_bids.bid_type ASC, players_bids.bid_value DESC, manager_points DESC, players_bids.bid_date ASC but the problem I have is that when referencing players_bids.users_id_from within the UNION I get back the following error : Error Code: 1054 Unknown column 'players_bids.users_id_from' in 'where clause' Any ideas how to overcome this problem ? Cheers Neil
If within If ?
Hi, I've the following query : SELECT IFNULL(SUM(gate_receipts),0) AS gate_receipts, competitions.caption FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' However, I want to check the value of competitions.competition_type and if it is 'cup' I want to divide the value of gate_receipts by 2. How is the best way to achieve this ? Regards Neil
Fwd: If within If ?
Hi, I think I've managed to do it : SELECT IF(competitions.competition_type = 'cup', IFNULL(ROUND(SUM(gate_receipts)/2),0), IFNULL(SUM(gate_receipts),0))AS gate_receipts FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' Cheers Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 19, 2010 at 2:01 PM Subject: If within If ? To: [MySQL] mysql@lists.mysql.com Hi, I've the following query : SELECT IFNULL(SUM(gate_receipts),0) AS gate_receipts, competitions.caption FROM fixtures_results INNER JOIN competitions ON fixtures_results.competitions_id = competitions.competitions_id WHERE home_teams_id = 27 AND worlds_id = 1 AND status = 'approved' However, I want to check the value of competitions.competition_type and if it is 'cup' I want to divide the value of gate_receipts by 2. How is the best way to achieve this ? Regards Neil
Fwd: Backing up MySQL using PHPMyAdmin problem with UTF-8
MySQL Administrator 1.2.15 is fails with the same problem. Do anyone have any other suggestions ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Fri, Oct 15, 2010 at 11:21 AM Subject: Re: Backing up MySQL using PHPMyAdmin problem with UTF-8 To: Michael Dykman mdyk...@gmail.com Cc: [MySQL] mysql@lists.mysql.com I managed to use the older version of MySQL Administrator 1.2.15 and it appears to back up find using InnoDB online backup. On Thu, Oct 14, 2010 at 6:56 PM, Michael Dykman mdyk...@gmail.com wrote: I have had this problem with PHPMyAdmin many times, and the only way I know around it, is to go in and do your dump at the console. PHP does not deal with UTF very well. - michael dykman On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I'm using PHP MyAdmin to backup my MySQL database. The database is of type InnoDB and encoding used is utf8_unicode_ci. The variables are set as follows : *MySQL connection collation: **utf8_unicode_ci* *MySQL charset: **UTF-8 Unicode (utf8)* *character set client: utf8* *character set connection: utf8* *character set results: utf8* *collation connection: **utf8_unicode_ci* The problem I have is that the foreign characters like ăÿć etc are being backed up as scrambled non-readable characters. Any ideas why this is happening ?? Are there any other variables I need to check/set ? Cheers Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you.
COUNT question
Hi, I've the following query SELECT COUNT(players_id) AS players_count FROM players WHERE teams_id 0 GROUP BY teams_id ORDER BY players_count DESC However, I've another field called original_teams_id and want to include the COUNT with players_count, when original_teams_id = teams_id Cheers Neil
Re: Backing up MySQL using PHPMyAdmin problem with UTF-8
Oh this isn't good. Because the database in on a shared server and I don't think I've got console access ? On Thu, Oct 14, 2010 at 6:56 PM, Michael Dykman mdyk...@gmail.com wrote: I have had this problem with PHPMyAdmin many times, and the only way I know around it, is to go in and do your dump at the console. PHP does not deal with UTF very well. - michael dykman On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I'm using PHP MyAdmin to backup my MySQL database. The database is of type InnoDB and encoding used is utf8_unicode_ci. The variables are set as follows : *MySQL connection collation: **utf8_unicode_ci* *MySQL charset: **UTF-8 Unicode (utf8)* *character set client: utf8* *character set connection: utf8* *character set results: utf8* *collation connection: **utf8_unicode_ci* The problem I have is that the foreign characters like ăÿć etc are being backed up as scrambled non-readable characters. Any ideas why this is happening ?? Are there any other variables I need to check/set ? Cheers Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Fwd: Primary key not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Oct 13, 2010 at 8:22 PM Subject: Re: Primary key not unique on InnoDB table To: Travis Ard travis_...@hotmail.com Cc: [MySQL] mysql@lists.mysql.com Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard travis_...@hotmail.com wrote: I couldn't help but notice you have individual indexes on nearly all the fields of your table. If you won't be using these fields exclusively as a join or filter condition in a query, you are unlikely to benefit from the extra indexes and, in fact, they could slow down your inserts and add to your storage requirements. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, October 13, 2010 8:37 AM To: [MySQL] Subject: Primary key not unique on InnoDB table I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
Re: Backing up MySQL using PHPMyAdmin problem with UTF-8
I managed to use the older version of MySQL Administrator 1.2.15 and it appears to back up find using InnoDB online backup. On Thu, Oct 14, 2010 at 6:56 PM, Michael Dykman mdyk...@gmail.com wrote: I have had this problem with PHPMyAdmin many times, and the only way I know around it, is to go in and do your dump at the console. PHP does not deal with UTF very well. - michael dykman On Thu, Oct 14, 2010 at 5:48 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I'm using PHP MyAdmin to backup my MySQL database. The database is of type InnoDB and encoding used is utf8_unicode_ci. The variables are set as follows : *MySQL connection collation: **utf8_unicode_ci* *MySQL charset: **UTF-8 Unicode (utf8)* *character set client: utf8* *character set connection: utf8* *character set results: utf8* *collation connection: **utf8_unicode_ci* The problem I have is that the foreign characters like ăÿć etc are being backed up as scrambled non-readable characters. Any ideas why this is happening ?? Are there any other variables I need to check/set ? Cheers Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Backing up MySQL using PHPMyAdmin problem with UTF-8
Hi, I'm using PHP MyAdmin to backup my MySQL database. The database is of type InnoDB and encoding used is utf8_unicode_ci. The variables are set as follows : *MySQL connection collation: **utf8_unicode_ci* *MySQL charset: **UTF-8 Unicode (utf8)* *character set client: utf8* *character set connection: utf8* *character set results: utf8* *collation connection: **utf8_unicode_ci* The problem I have is that the foreign characters like ăÿć etc are being backed up as scrambled non-readable characters. Any ideas why this is happening ?? Are there any other variables I need to check/set ? Cheers Neil
Fwd: Backing up the InnoDB tables
Would really appreciate some help or suggestions on this please, if anyone can assist ? Regards Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 12, 2010 at 5:45 PM Subject: Backing up the InnoDB tables To: [MySQL] mysql@lists.mysql.com Hi On a shared MySQL server with access just to my own database, what is the recommend backup methods and strategies for the InnoDB tables ? Cheers Neil
Re: Backing up the InnoDB tables
The problem is I don't have any command line access, just direct MySQL access to the database tables. On Wed, Oct 13, 2010 at 1:19 PM, Suresh Kuna sureshkumar...@gmail.comwrote: use xtra backup On Wed, Oct 13, 2010 at 5:37 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Would really appreciate some help or suggestions on this please, if anyone can assist ? Regards Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 12, 2010 at 5:45 PM Subject: Backing up the InnoDB tables To: [MySQL] mysql@lists.mysql.com Hi On a shared MySQL server with access just to my own database, what is the recommend backup methods and strategies for the InnoDB tables ? Cheers Neil -- Thanks Suresh Kuna MySQL DBA
Primary key not unique on InnoDB table
I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
Re: Primary key not unique on InnoDB table
Sorry Joao, I thought that was pretty standard to have a primary key with auto_increment ?? 2010/10/13 João Cândido de Souza Neto j...@consultorweb.cnt.br Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto João Cândido de Souza Neto j...@consultorweb.cnt.br escreveu na mensagem news:20101013144314.9787.qm...@lists.mysql.com... I´d never seen before a composed primary key that has an auto_increment field on it. May be I can be wrong but I think it wont work properly. As far as I know, if you have an auto_increment field it must be your single primary key. Am I wrong? -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Primary key not unique on InnoDB table
I see what you mean. Infact this is wrong and I will be dropping the second field in the primary key. 2010/10/13 João Cândido de Souza Neto j...@consultorweb.cnt.br A primary key with an auto_increment is ok, but I cant think about a primary key with two fiels where one of them is autoincrement. Am I completely wrong? -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com... Sorry Joao, I thought that was pretty standard to have a primary key with auto_increment ?? 2010/10/13 João Cândido de Souza Neto j...@consultorweb.cnt.br Sorry, the word is counpound instead of composed. -- João Cândido de Souza Neto João Cândido de Souza Neto j...@consultorweb.cnt.br escreveu na mensagem news:20101013144314.9787.qm...@lists.mysql.com... I´d never seen before a composed primary key that has an auto_increment field on it. May be I can be wrong but I think it wont work properly. As far as I know, if you have an auto_increment field it must be your single primary key. Am I wrong? -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com... I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Primary key not unique on InnoDB table
Of course, sorry totally stupid should I recognised that. Thanks Neil On Wed, Oct 13, 2010 at 3:46 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Neil, Yes, primary key is always unique. In your case, you are using composite key (players_id,default_teams_id). _Krishna On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
Re: Primary key not unique on InnoDB table
Shawn it is fine. I thought my primary key was just 1 field. On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 10/13/2010 11:37 AM, Tompkins Neil wrote: Shawn, sorry my error, I didn't realise I had two fields as the primary key That's misinformation. You can have multiple fields as a primary key. Show us what you think is duplicate data and I may be able to help you fix your definition -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: Primary key not unique on InnoDB table
Hi Travis, Thanks for your response. The fields which have indexes on, can be used on every other search, which is why I thought about creating them. Would you recommend against this ? Cheers Neil On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard travis_...@hotmail.com wrote: I couldn't help but notice you have individual indexes on nearly all the fields of your table. If you won't be using these fields exclusively as a join or filter condition in a query, you are unlikely to benefit from the extra indexes and, in fact, they could slow down your inserts and add to your storage requirements. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, October 13, 2010 8:37 AM To: [MySQL] Subject: Primary key not unique on InnoDB table I've the following table. But why isn't the primary key unique, e.g. preventing duplicates if entered ? CREATE TABLE `players_master` ( `players_id` bigint(20) NOT NULL AUTO_INCREMENT, `default_teams_id` bigint(20) NOT NULL, `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `dob` date NOT NULL, `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL, `retirement_date` date DEFAULT NULL, `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL, `estimated_value` double NOT NULL DEFAULT '0', `contract_wage` double NOT NULL DEFAULT '0', `rating` int(11) NOT NULL, PRIMARY KEY (`players_id`,`default_teams_id`), KEY `FK_players_master_countries_id` (`countries_id`), KEY `FK_players_master_positions_id` (`positions_id`), KEY `IDX_first_name` (`first_name`), KEY `IDX_known_as` (`known_as`), KEY `IDX_second_name` (`second_name`), KEY `IDX_dob` (`dob`), KEY `IDX_estimated_value` (`estimated_value`), KEY `IDX_contract_wage` (`contract_wage`), KEY `IDX_rating` (`rating`), KEY `FK_players_master_teams_id` (`default_teams_id`), CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`) REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`) REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`) REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci I'm confused, I thought primary keys were always unique ? Cheers Neil
Backing up the InnoDB tables
Hi On a shared MySQL server with access just to my own database, what is the recommend backup methods and strategies for the InnoDB tables ? Cheers Neil
Re: innodb backup
I'm interested in InnoDB backups. Does anyone use PHPMyAdmin ? I've a MySQL server on a shared hosting server. Cheers Neil On Sat, Oct 9, 2010 at 3:21 AM, short.cut...@yahoo.com.cn wrote: Hello, Is there any good document for backup of InnoDB? includes the increment backup and full backup. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Query working on 5.1.43 and not 5.0.77
Hi, My hosting company are only running MySQL version 5.0.77 and at this current time are not planning on upgrading to at least my required version of 5.1.43. My query in which I have some problems is shown below : SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,@team ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC Basically the part of the statement WHERE rank = 11 has no affect in version 5.0.77, but gives the desired results in version 5.1.43. Does anyone have any suggestions on how to overcome this problem, since at the current time I don't have the budget to move to another hosting company and this is the only query which I have a problem with. Thanks for any help. Cheers Neil
Fwd: Design advice
Wonder if anyone can help me ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Tue, Oct 5, 2010 at 10:07 AM Subject: Design advice To: [MySQL] mysql@lists.mysql.com Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Thanks, Neil
Re: Indexing question
Hi
Find attached the first of my queries, I require to be checked over. Let me
know if you need any more information, I've included the query, EXPLAIN
output, and the relavant SHOW CREATE TABLE
Regards
Neil
On Mon, Oct 4, 2010 at 9:50 PM, Neil Tompkins
neil.tompk...@googlemail.comwrote:
I've got a fair few number of queries to be checked over. Will send them
tommorrow
On 4 Oct 2010, at 18:27, Gavin Towey gto...@ffn.com wrote:
Include the query, EXPLAIN output, and the relavant SHOW CREATE TABLE
table \G output. Someone should be able to offer suggestions.
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Monday, October 04, 2010 8:54 AM
To: Joerg Bruehe
Cc: [MySQL]
Subject: Re: Indexing question
Jörg
Thanks for the useful reply. Maybe I can EXPLAIN my select queries for
you
to advise if any changes need to be made ?
Regards
Neil
On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe joerg.bru...@oracle.com
wrote:
Hi!
Neil Tompkins wrote:
Thanks for your reply. So should we create individual indexes on each
field or a multiple column index ??
This question cannot be answered without checking and measuring your
installation. The decision whether to create an index is always an act
of balancing:
- If there is an index, the database server can use it to find data
records by looking up the index, not scanning the base data.
This results in load reduction (both CPU and disk IO) and speeds up
query execution.
- If there is an index, the database server must maintain it whenever
data are altered (insert/update/delete), in addition to the base data.
This is increased load (both CPU and disk IO) and slows down data
changes.
So obviously you want to create only those indexes that are helpful for
query execution: you will never (voluntarily) create an index on a
column which isn't used in search conditions, or whose use is already
provided by other indexes.
Of the remaining candidate indexes, you will never (voluntarily) create
one that provides less gain in searches than it costs in data changes.
With MySQL, AFAIK there is the limitation that on one table only one
index can be used. As a result, the choice of indexes to create depends
on the searches executed by your commands, their relative frequency, and
the frequency of data changes.
To answer your other question: If you run aggregate functions (like
SUM(), MIN(), or MAX()) on all records of a table, their results could
be computed by accessing a matching index only. I don't know whether
MySQL does this, I propose you check that yourself using EXPLAIN.
If you run them on subsets of a table only, an index on that column will
not help in general.
In database implementations, there is the concept of a covering index:
If you have an index on columns A and B of some table, its contents
(without the base data) would suffice to answer
SELECT SUM(B) WHERE A = x
Again, I don't know whether MySQL does this, and I refer you to EXPLAIN.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com
ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603
This message contains confidential information and is intended only for
the individual named. If you are not the named addressee, you are notified
that reviewing, disseminating, disclosing, copying or distributing this
e-mail is strictly prohibited. Please notify the sender immediately by
e-mail if you have received this e-mail by mistake and delete this e-mail
from your system. E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or
omissions in the contents of this message, which arise as a result of e-mail
transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
94089, USA, FriendFinder.com
'players_bids', 'CREATE TABLE `players_bids` (
`players_bids_id` bigint(20) NOT NULL auto_increment,
`worlds_id` int(10) NOT NULL,
`seasons_id` int(10) NOT NULL,
`teams_id_from` bigint(20) NOT NULL,
`teams_id_to` bigint(20) NOT NULL,
`users_id_from` bigint(20) NOT NULL,
`users_id_to` bigint(20) NOT NULL,
`players_id` bigint(20) NOT NULL,
`bid_value` double NOT NULL default ''0'',
`bid_type` enum(''transfer'',''loan'',''exchange'') collate utf8_unicode_ci
NOT NULL,
`bid_status` varchar(45) collate utf8_unicode_ci NOT NULL,
`bid_date` datetime NOT NULL,
`bid_completed_date` datetime default NULL,
PRIMARY KEY (`players_bids_id`),
KEY `FK_players_bids_worlds_id` (`worlds_id`),
KEY `IDX_bid_date` (`bid_date`),
KEY `IDX_bid_status` (`bid_status`),
KEY `IDX_bid_type` (`bid_type`),
KEY
Design advice
Hi I have a number of tables of which I use to compute totals. For example I have table : players_master rec_id players_name teams_id rating I can easily compute totals for the field rating. However, at the end of a set period within my application, the values in the rating field are changed. As a result my computed totals would then be incorrect. Is the best way to overcome this problem to either compute the total and store as a total value (which wouldn't change in the future), or to store the rating values in a different table altogether and compute when required. If you need table information please let me know and I can send this. Thanks, Neil
Re: Indexing question
Jörg Thanks for the useful reply. Maybe I can EXPLAIN my select queries for you to advise if any changes need to be made ? Regards Neil On Mon, Oct 4, 2010 at 3:38 PM, Joerg Bruehe joerg.bru...@oracle.comwrote: Hi! Neil Tompkins wrote: Thanks for your reply. So should we create individual indexes on each field or a multiple column index ?? This question cannot be answered without checking and measuring your installation. The decision whether to create an index is always an act of balancing: - If there is an index, the database server can use it to find data records by looking up the index, not scanning the base data. This results in load reduction (both CPU and disk IO) and speeds up query execution. - If there is an index, the database server must maintain it whenever data are altered (insert/update/delete), in addition to the base data. This is increased load (both CPU and disk IO) and slows down data changes. So obviously you want to create only those indexes that are helpful for query execution: you will never (voluntarily) create an index on a column which isn't used in search conditions, or whose use is already provided by other indexes. Of the remaining candidate indexes, you will never (voluntarily) create one that provides less gain in searches than it costs in data changes. With MySQL, AFAIK there is the limitation that on one table only one index can be used. As a result, the choice of indexes to create depends on the searches executed by your commands, their relative frequency, and the frequency of data changes. To answer your other question: If you run aggregate functions (like SUM(), MIN(), or MAX()) on all records of a table, their results could be computed by accessing a matching index only. I don't know whether MySQL does this, I propose you check that yourself using EXPLAIN. If you run them on subsets of a table only, an index on that column will not help in general. In database implementations, there is the concept of a covering index: If you have an index on columns A and B of some table, its contents (without the base data) would suffice to answer SELECT SUM(B) WHERE A = x Again, I don't know whether MySQL does this, and I refer you to EXPLAIN. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
MySQL DB Version
Account Number : uk600724 Dear Sir/Madam, The MySQL database version which you have supplied to us is version 5.0.77. However, it would appear that we require version to be at least 5.1.43. How can we get our MySQL db upgraded to this version or greater ? Regards Neil Tompkins
Re: Indexing question
So if you have individual indexes for example field_1, field_2 and field_3
etc and then perform a search like
WHERE field_1 = 10
AND field_3 = 'abc'
This wouldn't improve the search ? You have to create a index for all
possible combined field searches ?
On Fri, Oct 1, 2010 at 9:35 PM, Gavin Towey gto...@ffn.com wrote:
You can't use an index to select records in a range, and order them. The
order by will cause a filesort in that case.
Additionally indexes are always read left to right. So an index on
('user_id', 'product_id') will help when doing WHERE user_id=N AND
product_id IN (1,2,3), but wouldn't help for just the condtion on
product_id.
See the manual for full details on how mysql uses indexes:
http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html
-Original Message-
From: Jonas Galvez [mailto:jonasgal...@gmail.com]
Sent: Friday, October 01, 2010 11:48 AM
To: mysql@lists.mysql.com
Subject: Indexing question
Suppose I wanted to be able to perform queries against three columns of my
table: 'user_id', 'product_id' and 'created'. Most of the time I'll just be
range-selecting records from the table ordering by 'created'. But I may
also
want to select where 'user_id' = something and 'product_id' in (list, of,
ids), ordered by 'created'. Do I need two separate indexes, one on
'created'
and another on ('user_id', 'product_id', 'created'), or does having only
the
latter suffice the former case?
-- Jonas, http://jonasgalvez.com.br
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your
system. E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive late
or incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in
the contents of this message, which arise as a result of e-mail
transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
94089, USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
SUM Top 10 records
Hi I've a basic table like and want to SUM the top 5 values. For example if I have id, rating 0, 10 1, 25 2, 5 3, 10 4, 50 5, 1 6, 15 7, 20 8, 9 I want my query to sum the values 4,50 1,25 7,20 6.15 0,10 Suming a value of 120 Any suggestions on how to achieve this ? Cheers Neil
Re: SUM Top 10 records
Christoph, this SUMs all values ? On Tue, Sep 28, 2010 at 11:25 AM, Christoph Boget christoph.bo...@gmail.com wrote: I've a basic table like and want to SUM the top 5 values. For example if I have Any suggestions on how to achieve this ? SELECT SUM( rating ) as total_rating FROM my-table ORDER BY rating DESC LIMIT 5 IIRC, that should work thnx, Christoph
Re: Advanced query help
Hi, I did try defining it before the IF statement, but still the same ? Cheers Neil On Mon, Sep 27, 2010 at 7:58 AM, Johan De Meersman vegiv...@tuxera.bewrote: At a guess, because you use @team in an if statement before you actually define it. On Sun, Sep 26, 2010 at 12:35 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following query SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC I'm running MySQL 5.0.77. However, if I run this query nothing is returned, but if I run the query again I get the desired results. Why is this ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Advanced query help
Hi, I've the following query SELECT teams_id AS teams_id ,SUM(rating) AS total_team_rating FROM (SELECT teams_id ,players_id ,rating ,IF(@team teams_id, @row := 1, @row := @row + 1) AS rank ,@team := teams_id FROM ( SELECT players.teams_id ,players.players_id ,players_master.rating FROM players JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 AND players.teams_id 0 ORDER BY players.teams_id, players_master.rating DESC) s1) s2 WHERE rank = 11 GROUP BY teams_id ORDER BY total_team_rating DESC I'm running MySQL 5.0.77. However, if I run this query nothing is returned, but if I run the query again I get the desired results. Why is this ? Cheers Neil
Matrix Stats Login
User fifaleagues Pass DrUch929eHek9Huwa7HAcH6wRUk5QaTr
Fwd: Matrix Stats Login
Sorry, this message was sent in error, please ignore. -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Fri, Sep 24, 2010 at 5:41 PM Subject: Matrix Stats Login To: [MySQL] mysql@lists.mysql.com User Pass
Query help please
Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called home_goals_aa, in my logic like IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , Cheers Neil
Fwd: Query help please
I wondered if anyone can help me ? Do you need any further information ? Cheers Neil -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Thu, Sep 23, 2010 at 9:49 AM Subject: Query help please To: [MySQL] mysql@lists.mysql.com Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called home_goals_aa, in my logic like IF(home_goals away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals away_goals, 1, 0) AS lost_home , Cheers Neil
SELECT WHERE IN help
Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil
Re: SELECT WHERE IN help
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Encryption with MYSQL
Any ideas why my statement like SELECT SHA1('abc') AS my_sha is being
returned as binary value, I thought it should be returning HEX in anycase ?
I'm using MySQL community server 5.1.43, with Innodb tables.
Cheers
Neil
On Sat, Sep 18, 2010 at 10:43 AM, Johan De Meersman vegiv...@tuxera.bewrote:
hex() and unhex() should do the trick in mysql (not base64, but same idea).
Those will transform every byte into a pair of hexadecimal digits - thus
limited to 0-9 and A-F - and reverse the process.
On Fri, Sep 17, 2010 at 2:13 PM, Tompkins Neil
neil.tompk...@googlemail.com wrote:
Sorry Johan, how exactly do you do this ?
On Fri, Sep 17, 2010 at 1:11 PM, Johan De Meersman vegiv...@tuxera.bewrote:
Simply base64-encode the returned binary string before offering it to
your client.
On Fri, Sep 17, 2010 at 1:22 PM, Tompkins Neil
neil.tompk...@googlemail.com wrote:
Hi,
I need to encrypt a string like 'hello world', using a passkey. But I
also
need to be able to decrypt the encrypted phrase using the same passkey.
I
noticed in MySQL there are functions like
AES_ENCRYPT()
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_aes-encrypt
However, I need the encrypted phrase to be returned as a string, since
it
will be passed to a URL as a parameter. Does anyone have any
suggestions on
how to overcome this issue, using MySQL.
I know that there are many components available out there, but I can't
install any third party components on the hosting server.
Cheers
Neil
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Encryption with MYSQL
Hi, I need to encrypt a string like 'hello world', using a passkey. But I also need to be able to decrypt the encrypted phrase using the same passkey. I noticed in MySQL there are functions like AES_ENCRYPT()http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_aes-encrypt However, I need the encrypted phrase to be returned as a string, since it will be passed to a URL as a parameter. Does anyone have any suggestions on how to overcome this issue, using MySQL. I know that there are many components available out there, but I can't install any third party components on the hosting server. Cheers Neil
SHA1 returns binary value
Hi
Why when I run the command (MySQL 5.1) SELECT SHA1('abc'); is it returned as
a binary value and not a string value ?
Cheers
Neil
Improve query
Hi, Regarding my query below, is there anyway in which this query can be improved ? SELECT t1.players_brought, t2.players_sold, t3.team_balance, t4.home_team_wages, t5.away_team_wages, (t3.team_balance+t2.players_sold)-(t1.players_brought+t4.home_team_wages+t5.away_team_wages) AS team_balance, t6.home_prize_money, t7.away_prize_money FROM(SELECT SUM(bid_value) players_brought FROM players_bids WHERE teams_id_from = 1 AND bid_status = 'approved' AND worlds_id = 1 ) AS t1, (SELECT SUM(bid_value) players_sold FROM players_bids WHERE teams_id_to = 1 AND bid_status = 'approved' AND worlds_id = 1 ) AS t2, (SELECT SUM(amount) AS team_balance FROM teams_finance WHERE teams_id = 1) AS t3, (SELECT SUM(home_team_wages) AS home_team_wages FROM fixtures_results WHERE home_teams_id = 1 AND status = 'approved') AS t4, (SELECT SUM(away_team_wages) AS away_team_wages FROM fixtures_results WHERE away_teams_id = 1 AND status = 'approved') AS t5, (SELECT SUM(prize_money) AS home_prize_money FROM fixtures_results WHERE home_teams_id = 1 AND status = 'approved') AS t6, (SELECT SUM(prize_money) AS away_prize_money FROM fixtures_results WHERE away_teams_id = 1 AND status = 'approved') AS t7 Cheers Neil
Fwd: Query SUM help
Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Re: Query SUM help
Yes, but it doesn't work. Basically I want the SUM(players_master.rating) only to SUM the top 11 players from each team. Any suggestions ? Cheers Neil On Thu, Sep 9, 2010 at 9:17 AM, Ananda Kumar anan...@gmail.com wrote: did u try to use LIMIT after ORDER BY On Thu, Sep 9, 2010 at 1:27 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Any help would be really appreciated ? -- Forwarded message -- From: Tompkins Neil neil.tompk...@googlemail.com Date: Wed, Sep 8, 2010 at 5:30 PM Subject: Query SUM help To: [MySQL] mysql@lists.mysql.com Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Table design help
Hi all, Needing some advice on my tables design. Basically I am designing a soccer application, and have a table which contains player_bids (the values of which a player costs to be transferred between clubs). Can someone please offer some input on the best way in which I should design the financial table for each team to hold the teams current balance,historic financial information in terms of player wages and how best I should link it to the players_bids table, if I should at all ? Other information storage in the financial table would be things like prize money, gate receipts etc Thanks for any help. Cheers Neil
Query SUM help
Hi I've the following query : SELECT total_team_rating, my_teams_id FROM (SELECT players.teams_id AS my_teams_id, SUM(players_master.rating) AS total_team_rating FROM players INNER JOIN players_master ON players.players_id = players_master.players_id WHERE players.worlds_id = 1 AND players.red_cards = 0 AND players.injury_duration_remaining = 0 GROUP BY players.teams_id) s1 ORDER BY s1.total_team_rating DESC This gives me the total of players_master.rating for each players.teams_id. However, I'm wanting to only base the players_master.rating on the top 11 records in the players table for each team. How can I modify my query to achieve this ? Thanks Neil
Re: Best method to keep totals
Just another quick question - following my initial question regarding the best method to keep totals, I wondered if I should adopt a table to record team league standings like based on each result like team_id home_win home_draw home_loss home_goals home_conceded away_win away_draw away_loss away_goals away_conceded Or should I try and display the league standings, based on the fixtures/results table which contains fields like match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Cheers Neil On Sun, Sep 5, 2010 at 7:32 AM, Neil Tompkins neil.tompk...@googlemail.comwrote: Thanks for all the useful information. I'm going to ensure the relevant fields are indexed and our db is optimised. On 4 Sep 2010, at 16:10, Arthur Fuller fuller.art...@gmail.com wrote: 100% agreed. Arthur The other exception is also where financial data is being stored. If you have, say, a database containing sales order records, then as well as storing the individual values of each item in each order, you also need to store the total value of the order, the total price charged to the customer and the total paid by the customer. These three should, of course, be not only identical to each other but also to the sum of the individual items, so there is not only duplication but the potential for skew. But that, of course, is precisely *why* you store them, as any discrepancy indicates an error which needs to be investigated. Mark
Query help
Hi, I've the following fields within a table : fixtures_results_id home_teams_id away_teams_id home_goals away_goals home_users_id away_users_id From this I want to extract the following type of information if the home_users_id or away_users_id = 1 : total number of games games number of games won number of games drawn number of games lost number of goals scored number of goals conceded biggest win biggest loss most goals in a game I'd appreciate any help with regards the query and whether or not it is possible to do ? Cheers Neil
Re: Query help
These two fields home_goals and away_goals Cheers Neil On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar anan...@gmail.com wrote: Tompkins, Which field stores the result of matches. regards anandkl On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following fields within a table : fixtures_results_id home_teams_id away_teams_id home_goals away_goals home_users_id away_users_id From this I want to extract the following type of information if the home_users_id or away_users_id = 1 : total number of games games number of games won number of games drawn number of games lost number of goals scored number of goals conceded biggest win biggest loss most goals in a game I'd appreciate any help with regards the query and whether or not it is possible to do ? Cheers Neil
Re: Query help
For sure here is some sample data home_teams_id,away_teams_id,home_goals,away_goals,home_users_id,away_users_id 100,200,2,1,5,6 200,100,1,1,6,5 Here is two rows of data for the same fixture both home and away Let me know if you need any more info. Cheers Neil On Mon, Sep 6, 2010 at 1:08 PM, Ananda Kumar anan...@gmail.com wrote: Also, can u please lets u know the value's in this table. Just one row, an example would do. regards anandkl On Mon, Sep 6, 2010 at 5:35 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: These two fields home_goals and away_goals Cheers Neil On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar anan...@gmail.com wrote: Tompkins, Which field stores the result of matches. regards anandkl On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following fields within a table : fixtures_results_id home_teams_id away_teams_id home_goals away_goals home_users_id away_users_id From this I want to extract the following type of information if the home_users_id or away_users_id = 1 : total number of games games number of games won number of games drawn number of games lost number of goals scored number of goals conceded biggest win biggest loss most goals in a game I'd appreciate any help with regards the query and whether or not it is possible to do ? Cheers Neil
Re: Dup Key Error Messages
Hi
You have a PRIMARY KEY of just the id field like PRIMARY KEY ( `id` ). You
need both ip and type to be the primary key
Cheers
Neil
On Mon, Sep 6, 2010 at 2:02 PM, Thorsten Heymann heym...@macnetix.dewrote:
Hi,
First, I'm new to this mailing list, hopefully I'll do my post the *right*
way.
I've a problem with duplicate key error messages. In my application I use a
table with multiple unique keys (ip_addr and print_name). Lets use this
example:
Table `device` :
CREATE TABLE `device` (
`id` INT NOT NULL AUTO_INCREMENT ,
`ip_addr` VARCHAR( 15 ) NOT NULL ,
`type` VARCHAR( 32 ) NOT NULL ,
`print_name` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`ip_addr` ,
`print_name`
)
) ENGINE = InnoDB;
Let's do for example this two queries.
INSERT INTO `device` (ip, type, print_name) VALUES ('192.168.0.1', 'pc',
'desktop1');
INSERT INTO `device` (ip, type, print_name) VALUES ('192.168.0.1',
'router', 'gw1');
(Note the duplicate ip 192.168.0.1)
My problem is the error message sent MySql Server if a duplicate key error
occurres during insert / update. I have one machine running mysqld 5.0.41
who produces this message:
Duplicate entry '192.168.0.1' for key 2.
On the other machine who runs mysqld 5.1.51 I got the following:
Duplicate entry '192.168.0.1' for key 'ip'.
Since my application does something like this:
If(String.find('key 2', err_msg)){
Print 'you have selected the ip twice'.
} else {
Print_generic_error();
}
... it will not run on mysql 5.1.51.
Digging through mysqld source, I found this behaviour handled in
sql/handler.cc and changed from printing key_nr to key.name between this
versions. :(
Is there a possible better, reliable way to detect what key is duplictated
as searching in the error message?
Greets Thorsten
MACNETIXhttp://www.macnetix.com/ GmbH
Huttenstr. 31
10553 Berlin
Tel. +49 (0) 30 34 34 678 - 0
Fax. +49 (0) 30 34 34 678 - 99
heym...@macnetix.demailto:heym...@macnetix.de
Sitz Gerichtsstand /Registered Office Place of Jurisdiction : Berlin
Registergericht/ Registered at: HRB 86505 B Berlin-Charlottenburg
Geschäftsführung/ Member of the Management Board: Dirk Wahrheit, Peggy
Bielke
Bitte beachten Sie unsere Webseite www.macnetix.dehttp://www.macnetix.de/
!
Diese Information ist ausschliesslich fuer den Adressaten bestimmt und kann
vertraulich oder gesetzlich geschuetzte Informationen enthalten. Wenn Sie
nicht der bestimmungsgemaesse Adressat sind, unterrichten Sie bitte den
Absender und vernichten Sie diese Mail. Anderen als dem bestimmungsgemaessen
Adressaten ist es untersagt, diese E-Mail zu lesen, zu speichern,
weiterzuleiten oder ihren Inhalt auf welche Weise auch immer zu verwenden.
Wir verwenden aktuelle Virenschutzprogramme. Fuer Schaeden, die dem
Empfaenger gleichwohl durch von uns zugesandte mit Viren befallene E-Mails
entstehen, schliessen wir jede Haftung aus.
The information contained in this email is intended only for its addressee
and may contain confidential and/or privileged information. If the reader of
this email is not the intended recipient, you are hereby notified that
reading, saving, distribution or use of the content of this email in any way
is prohibited. If you have received this email in error, please notify the
sender and delete the email. We use updated antivirus protection software.
We do not accept any responsibility for damages caused anyhow by viruses
transmitted via email.
Re: Calculating table standings
Thanks for your great response Travis. This is exactly what I was after. On Thu, Aug 26, 2010 at 6:56 PM, Travis Ard travis_...@hotmail.com wrote: I don't think there's anything inherently wrong with the way you've designed your table to store your match data. I don't have experience designing these kinds of applications, so maybe some others might have better advice for you. If you find your reporting is too slow or it is too awkward to query this table twice and union the results, then you might want to add a summary table. -Travis -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 2:06 PM To: Travis Ard Cc: [MySQL] Subject: Re: Calculating table standings Travis Do you think it would be better if I stored the information in a separate table, rather than using unions etc - to make the searching, counting etc easier ? Or is this method a standard way of dealing with this sort of data. Cheers Neil On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard travis_...@hotmail.com wrote: I think your match table has all the information necessary to display the results you want. Since each record contains data for two teams (home and away), you'd probably need to select each separately and union the results together before summarizing. Your query might look something like the following: select seasons_id ,team_id ,count(*) as games_played ,sum(home) as home_games_played ,sum(away) as away_games_played ,sum(won_home) as won_home ,sum(draw_home) as draw_home ,sum(lost_home) as lost_home ,sum(scored_home) as scored_home ,sum(conceded_home) as conceded_home ,sum(won_away) as won_away ,sum(draw_away) as draw_away ,sum(lost_away) as lost_away ,sum(scored_away) as scored_away ,sum(conceded_away) as conceded_away from (select seasons_id ,home_team_id as team_id ,1 as home ,0 as away ,if(home_goals away_goals, 1, 0) as won_home ,if(home_goals = away_goals, 1, 0) as draw_home ,if(home_goals away_goals, 1, 0) as lost_home ,home_goals as scored_home ,away_goals as conceded_home ,0 as won_away ,0 as draw_away ,0 as lost_away ,0 as scored_away ,0 as conceded_away from matches union all select seasons_id ,away_team_id as team_id ,0 as home ,1 as away ,0 as won_home ,0 as draw_home ,0 as lost_home ,0 as scored_home ,0 as conceded_home ,if(away_goals home_goals, 1, 0) as won_away ,if(away_goals = home_goals, 1, 0) as draw_away ,if(away_goals home_goals, 1, 0) as lost_away ,away_goals as scored_away ,home_goals as conceded_away from matches) s1 group by seasons_id, team_id; -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 24, 2010 7:45 AM To: [MySQL] Subject: Calculating table standings Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based on the above information, I'm wanting to generate a league table listing showing games_played won_home draw_home lost_home scored_home conceded_home won_away draw_away lost_away scored_away conceded_away Finally I also want a tally for the number of points e.g 3 points for win, 1 point for a draw. Do you think this is possible with the basic table I have, or should I consider putting the result data in a leagues table working out the fields I have listed above, and then just calculating it and display it ? Thanks for any advice. Cheers Neil
Fwd: Database design help
Looking for some help / comments if possible ? Cheers Neil -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Tue, Aug 31, 2010 at 8:48 PM Subject: Database design help To: mysql@lists.mysql.com Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil
Re: Database design help
I do have a tabled which contains both the managers_id and teams_id for the current teams managed. I think by adding the managers_id alongside the fixture_result table will then allow me to find which points the manager has accumulated alongside which fixtures and teams. Cheers Neil On Wed, Sep 1, 2010 at 4:43 PM, Jerry Schwartz je...@gii.co.jp wrote: I strongly suggest that you make a separate table for the manager - team relationship, so you can keep a history. Put a date-stamp in there. This might come in handy as you get further into your design. I ran into this problem when one of our sales reps moved from one office to another, and took their sales history with them! That was a mess to unscramble. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 3:48 PM To: mysql@lists.mysql.com Subject: Database design help Hi I've a soccer application consisting of managers, teams players and fixtures/results. Basically each manager will get points for each game which will depend on the result. What would be the best table design bearing in mind that a manager can move to a different club. My thought was to have a field in the fixtures/results table for the manager points but i think that I will also need a users field so that I can remember which points belong to which manager. Is this the correct approach?? Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
Best method to keep totals
Hi, I'm developing a system whereby a manager gets assigned points based a certain number of factors which are saved alongside a football result. However, I also want to keep a total for each manager. My question is it best to just have a query that uses SUM to total the managers points gained for each fixture participated in ? Or should I be looking to have a separate field which is the calculated total for each manager ? Thanks in advance for any advice. Cheers Neil
Re: Best method to keep totals
Thanks for your quick response. So like I thought, I'll just calculate the totals on the fly and like you mentioned the manager is going to participate in 50 games per season, with a season being twice per year. Thanks for the help. Neil On Wed, Sep 1, 2010 at 10:06 PM, Jerry Schwartz je...@gii.co.jp wrote: IMNSHO, never store dynamic data in a field unless you absolutely have to. There are going to be exceptions, but unless a manager is going to participate in hundreds of thousands of fixtures I don't think this is one of them. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, September 01, 2010 4:29 PM To: [MySQL] Subject: Best method to keep totals Hi, I'm developing a system whereby a manager gets assigned points based a certain number of factors which are saved alongside a football result. However, I also want to keep a total for each manager. My question is it best to just have a query that uses SUM to total the managers points gained for each fixture participated in ? Or should I be looking to have a separate field which is the calculated total for each manager ? Thanks in advance for any advice. Cheers Neil
MySQL SUM on two columns
Hi, I've the following basic SUM for our products based on a rating. SELECT SUM(products.rating) AS products_rating FROM products_sales INNER JOIN products ON products_sales.products_id = products.products_id WHERE products.enabled = 1 AND products_sales.language = 'EN' This works fine, however I also want to SUM the ES language within the same query. Is this possible to do, or should I just query the table twice, replacing the language parameter ? Thanks. Neil
Re: MySQL SUM on two columns
Sorry I don't want to SUM the varchar columns. This is the parameter passed that defines the two different queries. On Tue, Aug 31, 2010 at 11:48 AM, misiaQ mis...@poczta.fm wrote: I don't think that SUM will work for varchar columns. If I got your point right - you need to use WITH ROLLUP grouping http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html see second example on that page. regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 31, 2010 11:43 AM To: [MySQL] Subject: MySQL SUM on two columns Hi, I've the following basic SUM for our products based on a rating. SELECT SUM(products.rating) AS products_rating FROM products_sales INNER JOIN products ON products_sales.products_id = products.products_id WHERE products.enabled = 1 AND products_sales.language = 'EN' This works fine, however I also want to SUM the ES language within the same query. Is this possible to do, or should I just query the table twice, replacing the language parameter ? Thanks. Neil -- Saloon gier - to nas wyroznia! Sprawdz http://linkint.pl/f27e0
Re: MySQL SUM on two columns
Perfect. Exactly what I was looking for.
Cheers John.
Regards,
Neil
On Tue, Aug 31, 2010 at 12:06 PM, John Daisley
daisleyj...@googlemail.comwrote:
SELECT products_sales.language, SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language IN ('EN','ES')
GROUP BY products_sales.language
Will give the sum for each language.
On 31 August 2010 11:42, Tompkins Neil neil.tompk...@googlemail.comwrote:
Hi,
I've the following basic SUM for our products based on a rating.
SELECT SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language = 'EN'
This works fine, however I also want to SUM the ES language within the
same
query. Is this possible to do, or should I just query the table twice,
replacing the language parameter ?
Thanks.
Neil
--
John Daisley
Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer
Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk
Re: MySQL SUM on two columns
Hi,
I also wondered, if there is any way to LIMIT the products.rating for each
language to say the top 25 ?
Cheers
Neil
On Tue, Aug 31, 2010 at 12:11 PM, Tompkins Neil
neil.tompk...@googlemail.com wrote:
Perfect. Exactly what I was looking for.
Cheers John.
Regards,
Neil
On Tue, Aug 31, 2010 at 12:06 PM, John Daisley daisleyj...@googlemail.com
wrote:
SELECT products_sales.language, SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language IN ('EN','ES')
GROUP BY products_sales.language
Will give the sum for each language.
On 31 August 2010 11:42, Tompkins Neil neil.tompk...@googlemail.comwrote:
Hi,
I've the following basic SUM for our products based on a rating.
SELECT SUM(products.rating) AS products_rating
FROM products_sales
INNER JOIN products ON products_sales.products_id = products.products_id
WHERE products.enabled = 1
AND products_sales.language = 'EN'
This works fine, however I also want to SUM the ES language within the
same
query. Is this possible to do, or should I just query the table twice,
replacing the language parameter ?
Thanks.
Neil
--
John Daisley
Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer
Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk
Retrieve three columns in sub query
Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil
Re: Retrieve three columns in sub query
I thought as much, if anyone else can shed some light that would be great. If not, I'm going to have to write an additional query. 2010/8/25 João Cândido de Souza Neto j...@consultorweb.cnt.br As far as I know sub-queries intends to be an only one column and row result. -- João Cândido de Souza Neto Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem news:aanlkti=djkujcsg=kf29sjsp0yllhmhb02mqzdvzd...@mail.gmail.com... Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
Re: Retrieve three columns in sub query
Hi M, This appears to do exactly what I require. Thank you ! Cheers Neil On Wed, Aug 25, 2010 at 1:43 PM, misiaQ mis...@poczta.fm wrote: select ci.*, ct.* from (select name, countrycode from city) ci, country ct where ci.countrycode = ct.code Regards, m -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Wednesday, August 25, 2010 1:23 PM To: [MySQL] Subject: Retrieve three columns in sub query Hi Is it possible in MySQL 5.1 to retrieve three columns in a select sub-query like below : SELECT student_age, SELECT (student_subjects_id, random_mark, subject FROM student_subjects ORDER BY RAND(), LIMIT 1) FROM students WHERE student_age 10 ORDER BY RAND() LIMIT 1 I've looked and tried everything, but nothing seems to work... Cheers Neil -- Kup wlasne mieszkanie za 72 tys. zl. Sprawdz najlepsze oferty http://linkint.pl/f27c4
Table design question
Hi, I'm creating a application which hosts football matches and I want to record the player appearances, goals etc. I was thinking of having a record for each player as follows : appearance_id season_id player_id team_id competition_id appearance goals yellow_card red_card date_played Is this the sort of standard layout, recommended for such application. From here, I can then count number of appearances, goals scored etc within a season or for a team_id ? Cheers Neil
Re: Calculating table standings
Travis Do you think it would be better if I stored the information in a separate table, rather than using unions etc - to make the searching, counting etc easier ? Or is this method a standard way of dealing with this sort of data. Cheers Neil On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard travis_...@hotmail.com wrote: I think your match table has all the information necessary to display the results you want. Since each record contains data for two teams (home and away), you'd probably need to select each separately and union the results together before summarizing. Your query might look something like the following: select seasons_id ,team_id ,count(*) as games_played ,sum(home) as home_games_played ,sum(away) as away_games_played ,sum(won_home) as won_home ,sum(draw_home) as draw_home ,sum(lost_home) as lost_home ,sum(scored_home) as scored_home ,sum(conceded_home) as conceded_home ,sum(won_away) as won_away ,sum(draw_away) as draw_away ,sum(lost_away) as lost_away ,sum(scored_away) as scored_away ,sum(conceded_away) as conceded_away from (select seasons_id ,home_team_id as team_id ,1 as home ,0 as away ,if(home_goals away_goals, 1, 0) as won_home ,if(home_goals = away_goals, 1, 0) as draw_home ,if(home_goals away_goals, 1, 0) as lost_home ,home_goals as scored_home ,away_goals as conceded_home ,0 as won_away ,0 as draw_away ,0 as lost_away ,0 as scored_away ,0 as conceded_away from matches union all select seasons_id ,away_team_id as team_id ,0 as home ,1 as away ,0 as won_home ,0 as draw_home ,0 as lost_home ,0 as scored_home ,0 as conceded_home ,if(away_goals home_goals, 1, 0) as won_away ,if(away_goals = home_goals, 1, 0) as draw_away ,if(away_goals home_goals, 1, 0) as lost_away ,away_goals as scored_away ,home_goals as conceded_away from matches) s1 group by seasons_id, team_id; -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 24, 2010 7:45 AM To: [MySQL] Subject: Calculating table standings Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based on the above information, I'm wanting to generate a league table listing showing games_played won_home draw_home lost_home scored_home conceded_home won_away draw_away lost_away scored_away conceded_away Finally I also want a tally for the number of points e.g 3 points for win, 1 point for a draw. Do you think this is possible with the basic table I have, or should I consider putting the result data in a leagues table working out the fields I have listed above, and then just calculating it and display it ? Thanks for any advice. Cheers Neil
Calculating table standings
Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based on the above information, I'm wanting to generate a league table listing showing games_played won_home draw_home lost_home scored_home conceded_home won_away draw_away lost_away scored_away conceded_away Finally I also want a tally for the number of points e.g 3 points for win, 1 point for a draw. Do you think this is possible with the basic table I have, or should I consider putting the result data in a leagues table working out the fields I have listed above, and then just calculating it and display it ? Thanks for any advice. Cheers Neil
SUM value like 10,23,15,10
Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil
Re: SUM value like 10,23,15,10
Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.bewrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SUM value like 10,23,15,10
The application is still being developed, so I will probably look at storing it in separate tables so that it can easily be computed. On Tue, Aug 24, 2010 at 3:01 PM, Johan De Meersman vegiv...@tuxera.bewrote: Then you're pretty much on your own, I'm afraid. Not a very good way to store data :-) You could maybe build a stored procedure, or do it in the app; but it's gonna be code either way. On Tue, Aug 24, 2010 at 3:58 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.be wrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Calculating table standings
Travis Thanks for the response. Do you recommend I store the data in this way ? Or should I look at storing in a separate leagues table, why by I list all the data in the separate columns for each round and then just compute a fairly basic query ? What is the recommend way ? Cheers Neil On Tue, Aug 24, 2010 at 5:43 PM, Travis Ard travis_...@hotmail.com wrote: I think your match table has all the information necessary to display the results you want. Since each record contains data for two teams (home and away), you'd probably need to select each separately and union the results together before summarizing. Your query might look something like the following: select seasons_id ,team_id ,count(*) as games_played ,sum(home) as home_games_played ,sum(away) as away_games_played ,sum(won_home) as won_home ,sum(draw_home) as draw_home ,sum(lost_home) as lost_home ,sum(scored_home) as scored_home ,sum(conceded_home) as conceded_home ,sum(won_away) as won_away ,sum(draw_away) as draw_away ,sum(lost_away) as lost_away ,sum(scored_away) as scored_away ,sum(conceded_away) as conceded_away from (select seasons_id ,home_team_id as team_id ,1 as home ,0 as away ,if(home_goals away_goals, 1, 0) as won_home ,if(home_goals = away_goals, 1, 0) as draw_home ,if(home_goals away_goals, 1, 0) as lost_home ,home_goals as scored_home ,away_goals as conceded_home ,0 as won_away ,0 as draw_away ,0 as lost_away ,0 as scored_away ,0 as conceded_away from matches union all select seasons_id ,away_team_id as team_id ,0 as home ,1 as away ,0 as won_home ,0 as draw_home ,0 as lost_home ,0 as scored_home ,0 as conceded_home ,if(away_goals home_goals, 1, 0) as won_away ,if(away_goals = home_goals, 1, 0) as draw_away ,if(away_goals home_goals, 1, 0) as lost_away ,away_goals as scored_away ,home_goals as conceded_away from matches) s1 group by seasons_id, team_id; -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, August 24, 2010 7:45 AM To: [MySQL] Subject: Calculating table standings Hi, I wondered if anyone can offer me some help with regards the following issue I'm having. Basically, I've the following table structure containing rows of results between two football teams. The fields are match_id seasons_id week_number home_team_id away_team_id home_goals away_goals Based on the above information, I'm wanting to generate a league table listing showing games_played won_home draw_home lost_home scored_home conceded_home won_away draw_away lost_away scored_away conceded_away Finally I also want a tally for the number of points e.g 3 points for win, 1 point for a draw. Do you think this is possible with the basic table I have, or should I consider putting the result data in a leagues table working out the fields I have listed above, and then just calculating it and display it ? Thanks for any advice. Cheers Neil
Re: Fixture List generation using MySQL
Gavin, Thanks for the great reply, this is actually what I was looking for. However, do you have any suggestions on how to order the fixtures / teams ? Basically the query is returning the teams grouped together like : '2', '1' '3', '1' '4', '1' '1', '2' '3', '2' '4', '2' '1', '3' '2', '3' '4', '3' '1', '4' '2', '4' '3', '4' But ideally I'm looking for the data to be returned like 2 v 1 3 v 4 1 v 3 4 v 2 etc Any suggestions ? Cheers Neil On Thu, Aug 19, 2010 at 6:50 PM, Gavin Towey gto...@ffn.com wrote: That's almost a cartesean product; except you just want to eliminate results where a team would be paired up with itself. create table teams ( id serial ); Query OK, 0 rows affected (0.02 sec) insert into teams values (), (), (), (); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 [ff] test select * from teams; ++ | id | ++ | 1 | | 2 | | 3 | | 4 | ++ 4 rows in set (0.00 sec) select * from locations; +--+ | name | +--+ | home | | away | +--+ 2 rows in set (0.00 sec) select * from teams t1 JOIN teams t2; +++ | id | id | +++ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 2 | | 1 | 3 | | 2 | 3 | | 3 | 3 | | 4 | 3 | | 1 | 4 | | 2 | 4 | | 3 | 4 | | 4 | 4 | +++ 16 rows in set (0.00 sec) With no join condition, we every possible combination of t1 paired with t2; however, this leads to the undesireable result that we have combinations like team 4 vs team 4. So you just need to add a condition to prevent those rows from showing up: select * from teams t1 JOIN teams t2 ON t1.id!=t2.id; +++ | id | id | +++ | 2 | 1 | | 3 | 1 | | 4 | 1 | | 1 | 2 | | 3 | 2 | | 4 | 2 | | 1 | 3 | | 2 | 3 | | 4 | 3 | | 1 | 4 | | 2 | 4 | | 3 | 4 | +++ 12 rows in set (0.10 sec) Notice you get both combinations of 2 vs 1 and 1 vs 2, so you could just call whichever team is in the first column as the home team. Regards, Gavin Towey -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, August 19, 2010 10:07 AM To: [MySQL] Subject: Re: Fixture List generation using MySQL I'm looking at a routine / script to create the fixtures like team 1 vs team 2 team 3 vs team 4 team 5 vs team 6 etc On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley peter.braw...@earthlink.net wrote: I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Basically ... select a.id,b.id from tbl a join tbl b on a.idb.id; union select a.id,b.id from tbl a join tbl b on a.idb.id; PB - On 8/19/2010 9:12 AM, Tompkins Neil wrote: Hi, I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Thanks for any input. Regards Neil This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
Fixture List generation using MySQL
Hi, I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Thanks for any input. Regards Neil
Re: Fixture List generation using MySQL
I'm looking at a routine / script to create the fixtures like team 1 vs team 2 team 3 vs team 4 team 5 vs team 6 etc On Thu, Aug 19, 2010 at 3:44 PM, Peter Brawley peter.braw...@earthlink.net wrote: I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Basically ... select a.id,b.id from tbl a join tbl b on a.idb.id; union select a.id,b.id from tbl a join tbl b on a.idb.id; PB - On 8/19/2010 9:12 AM, Tompkins Neil wrote: Hi, I'm tasked with generating a list of fixtures from a table of teams, whereby each team plays each other home and away. Does anyone have any experience generating such information using MySQL ? Thanks for any input. Regards Neil
