Sorry guys,
here is a table description, query and result:
SHOW CREATE TABLE Properties;
Properties |CREATE TABLE `Properties` (
`Property_ID` int(11) NOT NULL auto_increment,
`Insertion_Date` date default NULL,
`Status` varchar(20) default NULL,
`Uploader_ID` int(11) default NULL,
`Approver_ID` int(11) default NULL,
`Property_Name_Or_Number` varchar(50) default NULL,
`Address_Line_1` varchar(50) default NULL,
`Address_Line_2` varchar(50) default NULL,
`City` varchar(50) default NULL,
`County` varchar(50) default NULL,
`Postcode` varchar(12) default NULL,
`Asking_Price` int(11) default NULL,
`Date_On_Market` date default NULL,
`Sale_Price` int(11) default NULL,
`Exchange_Date` date default NULL,
`Tenure_ID` int(11) default NULL,
`Years_On_Lease` int(11) default NULL,
`Tax_Band_ID` int(11) default NULL,
`Age_ID` int(11) default NULL,
`Type_ID` int(11) default NULL,
`Number_Of_Bedrooms` int(11) default NULL,
`Number_Of_Bathrooms` int(11) default NULL,
`Number_Of_Receptions` int(11) default NULL,
`Number_Of_Kitchens` int(11) default NULL,
`Desirability_Of_Area_ID` int(11) default NULL,
`Internal_Condition_ID` int(11) default NULL,
`External_Condition_ID` int(11) default NULL,
`Plot_Size_ID` int(11) default NULL,
`Internal_Square_Footage` decimal(11,2) default NULL,
`Internal_Square_Meters` decimal(11,2) default NULL,
`Price_Per_Square_Foot` decimal(6,2) default NULL,
`Price_Per_Square_Meter` decimal(6,2) default NULL,
`Forced_Sale` char(3) NOT NULL default 'No',
`Sheltered_Accommodation` char(3) NOT NULL default 'No',
`Direct_From_Developer` char(3) NOT NULL default 'No',
`Reposession` char(3) NOT NULL default 'No',
`Requires_Updating` char(3) NOT NULL default 'No',
`Ex_Local_Authority` char(3) NOT NULL default 'No',
`Requires_Modernisation` char(3) NOT NULL default 'No',
`Executors_Sale` char(3) NOT NULL default 'No',
`Deposit_Paid_Or_Part_Exchange` char(3) NOT NULL default 'No',
`Shared_Equity` char(3) NOT NULL default 'No',
`Ex_Show_Home_Or_Site_Office` char(3) NOT NULL default 'No',
`Tenanted_Or_Part_Tenanted` char(3) NOT NULL default 'No',
`Auction_Sale` char(3) NOT NULL default 'No',
`Listed_Building` char(3) NOT NULL default 'No',
`Grade_ID` int(11) default NULL,
`Conservation_Area` char(3) NOT NULL default 'No',
`Number_Of_Garages` int(11) default NULL,
`Resident_Parking` char(3) NOT NULL default 'No',
`Number_Of_Parking_Spaces` int(11) default NULL,
`Other_Factors_Affecting_Sale` text,
`Measurement_Type` varchar(10) default NULL,
PRIMARY KEY (`Property_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> SELECT @SourceX := PC.X_COORD, @SourceY := PC.Y_COORD, @TargetR :=
O.Radius
-> FROM Postcodes PC
-> INNER JOIN Offices O
-> ON SUBSTRING(O.Postcode, 1, LOCATE(' ', O.Postcode) + 1 ) =
PC.PCDSECT
-> INNER JOIN Users U
-> ON U.Office_ID = O.Office_ID
-> AND U.User_ID = 183;
+------------------------+------------------------+----------------------+
| @SourceX := PC.X_COORD | @SourceY := PC.Y_COORD | @TargetR := O.Radius |
+------------------------+------------------------+----------------------+
| 292312.9 | 92463.8 | 1 |
+------------------------+------------------------+----------------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TEMPORARY TABLE tmpShortList (KEY(PCDSECT)) SELECT
-> PC.PCDSECT,
-> (@SourceX - PC.X_COORD) X_DIFF,
-> (@SourceY - PC.Y_COORD) Y_DIFF
-> FROM Postcodes PC
-> WHERE PC.X_COORD BETWEEN (@SourceX - (1609 * @TargetR)) AND (@SourceX
+ (1609 * @TargetR))
-> AND PC.Y_COORD BETWEEN (@SourceY - (1609 * @TargetR)) AND (@SourceY +
(1609 * @TargetR))
-> HAVING SQRT(X_DIFF*X_DIFF + Y_DIFF*Y_DIFF) <= (1609 * @TargetR);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT DATE_FORMAT(P1.Insertion_Date, "%D of %M %Y") AS "Insertion
Date",
-> S.Name AS "Uploaded By",
-> P1.Property_Name_Or_Number AS "Name or Number",
-> P1.Address_Line_1 AS "Address Line 1",
-> P1.Address_Line_2 AS "Address Line 2",
-> P1.City AS "City", P1.County AS "County",
-> P1.Postcode AS "Postcode",
-> CONCAT("", P1.Asking_Price) AS "Asking Price",
-> CONCAT("", P1.Sale_Price) AS "Sale Price",
-> DATE_FORMAT(P1.Date_On_Market, "%D of %M %Y") AS "Date on Market",
-> DATE_FORMAT(P1.Exchange_Date, "%D of %M %Y") AS "Exchange Date",
-> T1.Tenure AS "Tenure", P1.Years_On_Lease AS "Years on Lease",
-> T2.Tax_Band AS "Tax Band",
-> A.Age AS "Age",
-> C.Category AS "Category",
-> T3.Type AS "Type",
-> P1.Number_Of_Bedrooms AS "Number of Bedrooms",
-> P1.Number_Of_Bathrooms AS "Number of Bathrooms",
-> P1.Number_Of_Receptions AS "Number of Bathrooms",
-> P1.Number_Of_Kitchens AS "Number of Kitchens",
-> D.Desirability_Of_Area_ID AS "Desirability of Area",
-> I.Internal_Condition_ID AS "Internal Condition",
-> E.External_Condition_ID AS "External Condition",
-> P2.Plot_Size_ID AS "Plot Size",
-> P1.Internal_Square_Footage AS "Internal Square Footage",
-> P1.Internal_Square_Meters AS "Internal Squery Meters",
-> P1.Price_Per_Square_Foot AS "Price Per Square Foot",
-> P1.Price_Per_Square_Meter AS "Price Per Square Meter",
-> P1.Forced_Sale AS "Forced Sale",
-> P1.Reposession AS "Reposession",
-> P1.Requires_Updating AS "Requires Updating",
-> P1.Ex_Local_Authority AS "Ex Local Authority",
-> P1.Requires_Modernisation AS "Requires Modernisation",
-> P1.Executors_Sale AS "Executors Sale",
-> P1.Deposit_Paid_Or_Part_Exchange AS "Deposit Paid Or Part Exchange",
-> P1.Shared_Equity AS "Shared Equity",
-> P1.Ex_Show_Home_Or_Site_Office AS "Ex Show Home Or Site Office",
-> P1.Tenanted_Or_Part_Tenanted AS "Tenanted Or Part Tenanted",
-> P1.Auction_Sale AS "Auction Sale",
-> P1.Listed_Building AS "Listed Building",
-> G.Grade AS "Grade",
-> P1.Conservation_Area AS "Conservation Area",
-> P1.Number_Of_Garages AS "Number of Garages",
-> P1.Resident_Parking AS "Residents Parking",
-> P1.Number_Of_Parking_Spaces AS "Number Of Parking Spaces",
-> P1.Other_Factors_Affecting_Sale AS "Other_Factors_Affecting_Sale"
-> FROM Properties P1 INNER JOIN tmpShortList SL ON
SUBSTRING(P1.Postcode, 1, LOCATE(' ', P1.Postcode) + 1 ) = SL.PCDSECT, Ages
A, Users U, Offices O, Subscribers S, Tenures T1, Tax_Bands T2, Categories
C, Types T3, Desirability_Of_Areas D, Internal_Conditions I,
External_Conditions E, Plot_Sizes P2, Grades G WHERE P1.Status = "Approved"
AND P1.Uploader_ID = U.User_ID AND U.Office_ID = O.Office_ID AND
O.Subscriber_ID = S.Subscriber_ID AND P1.Tenure_ID = T1.Tenure_ID AND
P1.Tax_Band_ID = T2.Tax_Band_ID AND P1.Age_ID = A.Age_ID AND P1.Type_ID =
T3.Type_ID AND C.Category_ID = T3.Category_ID AND P1.Desirability_Of_Area_ID
= D.Desirability_Of_Area_ID AND P1.Internal_Condition_ID =
I.Internal_Condition_ID AND P1.External_Condition_ID =
E.External_Condition_ID AND P1.Plot_Size_ID = P2.Plot_Size_ID AND
P1.Grade_ID = G.Grade_ID LIMIT 1;
+--------------------+----------------------+----------------+------------------+----------------+--------+--------+----------+--------------+------------+-------------------+------------------+----------+----------------+-----------+--------+----------+---------------+--------------------+---------------------+---------------------+--------------------+----------------------+--------------------+--------------------+-----------+-------------------------+------------------------+-----------------------+------------------------+-------------+-------------+-------------------+--------------------+------------------------+----------------+-------------------------------+---------------+-----------------------------+---------------------------+--------------+-----------------+-----------+-------------------+-------------------+-------------------+--------------------------+------------------------------------------------------------------------------------------------------------------+
| Insertion Date | Uploaded By | Name or Number | Address Line
1 | Address Line 2 | City | County | Postcode | Asking Price | Sale
Price | Date on Market | Exchange Date | Tenure | Years on Lease |
Tax Band | Age | Category | Type | Number of Bedrooms | Number
of Bathrooms | Number of Bathrooms | Number of Kitchens | Desirability of
Area | Internal Condition | External Condition | Plot Size | Internal Square
Footage | Internal Squery Meters | Price Per Square Foot | Price Per Square
Meter | Forced Sale | Reposession | Requires Updating | Ex Local Authority |
Requires Modernisation | Executors Sale | Deposit Paid Or Part Exchange |
Shared Equity | Ex Show Home Or Site Office | Tenanted Or Part Tenanted |
Auction Sale | Listed Building | Grade | Conservation Area | Number of
Garages | Residents Parking | Number Of Parking Spaces |
Other_Factors_Affecting_Sale
|
+--------------------+----------------------+----------------+------------------+----------------+--------+--------+----------+--------------+------------+-------------------+------------------+----------+----------------+-----------+--------+----------+---------------+--------------------+---------------------+---------------------+--------------------+----------------------+--------------------+--------------------+-----------+-------------------------+------------------------+-----------------------+------------------------+-------------+-------------+-------------------+--------------------+------------------------+----------------+-------------------------------+---------------+-----------------------------+---------------------------+--------------+-----------------+-----------+-------------------+-------------------+-------------------+--------------------------+------------------------------------------------------------------------------------------------------------------+
| x | x | 9 | x| x| x | x |x| x | x | x1998 | x| x |
0 | Not Known | x | x | x | 6 | 2 |
5 | NULL | 2 | 3 |
2 | 4 | x| x | x |
x | No | No | No | No | No
| No | No | No | No
| No | No | Yes | Not Known |
No | NULL | No |
NULL | x |
+--------------------+----------------------+----------------+------------------+----------------+--------+--------+----------+--------------+------------+-------------------+------------------+----------+----------------+-----------+--------+----------+---------------+--------------------+---------------------+---------------------+--------------------+----------------------+--------------------+--------------------+-----------+-------------------------+------------------------+-----------------------+------------------------+-------------+-------------+-------------------+--------------------+------------------------+----------------+-------------------------------+---------------+-----------------------------+---------------------------+--------------+-----------------+-----------+-------------------+-------------------+-------------------+--------------------------+------------------------------------------------------------------------------------------------------------------+
1 row in set (20.84 sec)
mysql>
"Gleb Paharenko" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello.
>
> You will get much more help from the list if you provide the current
> table structure, short description of the information which is stored
> in that fields, and the query you want to optimize (most probably it
> takes much more time than others).
>
>> If point 1 is true then is there a way to trim all whitespace data?
>
> There are LTRIM and RTRIM functions. See:
> http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
>
>
>
>
>
> Shaun wrote:
>> Hi,
>>
>> I am trying to optimize my data and am currently looking at indexes etc.
>> Someone has suggested the following as well:
>>
>> 1. Ensure that there is no whitespace at the beginning or end of entries
>> 2. Use 1 or 0 instead of yes or no.
>>
>> If point 1 is true then is there a way to trim all whitespace data? The
>> problem I see with point 2 is it makes querying the data more awkward to
>> replace every 1 with a yes and 0 with a no.
>>
>> I would be most grateful for your comments on this.
>>
>>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> <___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
