Hello.
Taking into an account the nature of your queries, I can forward you to
these links:
http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html
If some of your fields have only two values ('Yes', 'No') you can
switch to ENUM type, it should improve the performance. See:
http://dev.mysql.com/doc/refman/5.0/en/enum.html
Use EXPLAIN to find out if your queries use proper indexes:
http://dev.mysql.com/doc/refman/5.0/en/explain.html
Why are you using HAVING if you do not have GROUP BY in
your 'CREATE TEMPORARY...' query? See:
http://dev.mysql.com/doc/refman/5.0/en/select.html
Shaun wrote:
> 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
>
>
>
--
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]
