#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    13
    Rep Power
    0

    Newbie to MySQL, needs help with query


    I have a database migrated from MS ACCESS (where this works fine) to MySQL 5.5 and this code was working, but it just stopped. It is a database and ASP web pages for our Security folks to use to record incdents here at our library. This just retrieves info on a current incident (when supplying the incident number frm link) from the table securitydb, an info from the perps table.

    Can this be simplified or written better?

    ---code---
    SELECT `securitydb`.`IncidentNumber` AS `IncidentNumber`
    , `securitydb`.`Autonumber` AS `AutoNumber`
    , `securitydb`.`Perp1` AS `Perp1`
    , `qry_perp_search1`.`picture` AS `perp1pic`
    , `qry_perp_search1`.`thumbs` AS `Perp1tmb`
    , `securitydb`.`Perp2` AS `Perp2`
    , `qry_perp_search2`.`picture` AS `perp2pic`
    , `qry_perp_search2`.`thumbs` AS `Perp2tmb`
    , `securitydb`.`Perp3` AS `Perp3`
    , `qry_perp_search3`.`picture` AS `perp3pic`
    , `qry_perp_search3`.`thumbs` AS `Perp3tmb`
    , `securitydb`.`Perp4` AS `Perp4`
    , `qry_perp_search4`.`picture` AS `perp4pic`
    , `qry_perp_search4`.`thumbs` AS `perp4tmb`
    , `securitydb`.`Perp5` AS `Perp5`
    , `qry_perp_search5`.`picture` AS `perp5pic`
    , `qry_perp_search5`.`thumbs` AS `perp5tmb`
    , `securitydb`.`Perp6` AS `Perp6`
    , `qry_perp_search6`.`picture` AS `perp6pic`
    , `qry_perp_search6`.`thumbs` AS `Perp6tmb`
    , `securitydb`.`Perp7` AS `Perp7`
    , `securitydb`.`Perp8` AS `Perp8`
    , `securitydb`.`Perp9` AS `Perp9`
    , `securitydb`.`Perp10` AS `Perp10`
    , `securitydb`.`problem` AS `problem`
    , `securitydb`.`victim` AS `victim`
    , `securitydb`.`Narrative` AS `Narrative`
    , `securitydb`.`Summary` AS `Summary`
    , `securitydb`.`PoliceCalled` AS `PoliceCalled`
    , `securitydb`.`FireCalled` AS `FireCalled`
    , `securitydb`.`AmbulanceCalled` AS `AmbulanceCalled`
    , `securitydb`.`FiledBy` AS `FiledBy`
    , date_format(`securitydb`.`DateFiled`, '%Y-%m-%d') AS `DateFiled`
    , `securitydb`.`Location` AS `Location`
    , `securitydb`.`Witness` AS `Witness`
    , `securitydb`.`OfficersName` AS `OfficersName`
    , `securitydb`.`BadgeNumber` AS `BadgeNumber`
    , `securitydb`.`CaseNumber` AS `CaseNumber`
    , `securitydb`.`disposition` AS `disposition`
    , `securitydb`.`Expelled` AS `Expelled`
    , date_format(`securitydb`.`ExpulsionDate`, '%Y-%m-%d') AS `ExpulsionDate`
    , date_format(`securitydb`.`ExpulsionEnd`, '%Y-%m-%d') AS `ExpulsionEnd`
    , `securitydb`.`ExpelledDays` AS `ExpelledDays`
    , `securitydb`.`SpecialNotes` AS `SpecialNotes`
    , date_format(`securitydb`.`dateofevent`, '%Y-%m-%d') AS `dateofevent`
    , `securitydb`.`dayofweek` AS `dayofweek`
    , `securitydb`.`etime` AS `etime`
    , `securitydb`.`ampm` AS `ampm`
    , date_format(`securitydb`.`jdate`, '%Y-%m-%d') AS `jdate`
    , `perps`.`fullname` AS `fullname`
    , `perps`.`picture` AS `picture`
    FROM
    (((((((`securitydb`
    LEFT JOIN `perps`
    ON ((convert(`securitydb`.`Perp1` USING utf8) = `perps`.`fullname`)))
    LEFT JOIN `qry_perp_search1`
    ON ((`securitydb`.`Perp1` = `qry_perp_search1`.`Perp1`)))
    LEFT JOIN `qry_perp_search2`
    ON ((`securitydb`.`Perp2` = `qry_perp_search2`.`Perp2`)))
    LEFT JOIN `qry_perp_search3`
    ON ((`securitydb`.`Perp3` = `qry_perp_search3`.`Perp3`)))
    LEFT JOIN `qry_perp_search4`
    ON ((`securitydb`.`Perp4` = `qry_perp_search4`.`Perp4`)))
    LEFT JOIN `qry_perp_search5`
    ON ((`securitydb`.`Perp5` = `qry_perp_search5`.`Perp5`)))
    LEFT JOIN `qry_perp_search6`
    ON ((`securitydb`.`Perp6` = `qry_perp_search6`.`Perp6`)))

    ---End Code---

    What am I doing wrong here? When this executes, it MAX's the CPU on database server.

    Thanks
    TB
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    You likely don't have correct indexes on your tables. Did you create them when you created the tables? If not you can add them with ALTER TABLE syntax.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    13
    Rep Power
    0
    You are correct, I do not have indexes created. I am using dbStudio for MySQL and I am unclear as their creation. Can you suggest?

    T
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by tbarkdull
    You are correct, I do not have indexes created. I am using dbStudio for MySQL and I am unclear as their creation.
    If everything else fails, read the manual:

    http://dev.mysql.com/doc/refman/5.5/en/create-index.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    13
    Rep Power
    0
    I have created indexes on both tables (on the primary keys) and I am still seeing the same issue. By the way, each of the JOINS at the bottom of the above query looks like this

    ---code---
    SELECT `securitydb`.`IncidentNumber` AS `IncidentNumber`
    , `securitydb`.`Perp1` AS `Perp1`
    , `perps`.`LastName` AS `LastName`
    , `perps`.`FirstName` AS `FirstName`
    , `perps`.`fullname` AS `fullname`
    , `perps`.`picture` AS `picture`
    , `securitydb`.`dateofevent` AS `dateofevent`
    , `perps`.`aka` AS `aka`
    , `perps`.`thumbs` AS `thumbs`
    , `securitydb`.`Expelled` AS `Expelled`
    FROM
    (`perps`
    JOIN `securitydb`
    ON ((`perps`.`fullname` = convert(`securitydb`.`Perp1` USING utf8))))

    ---code end---

    Should I be indexing on other fields? do I need to modifying the queries to use the indexes?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    13
    Rep Power
    0
    I agree, reading the manual is a necessity.

    Additionally, getting error

    "MySQL client ran out of memory"
  12. #7
  13. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    Looking at that query it really screams out that you are probably not normalizing your data.

    Also why do you need to run convert on the full name field?

    Show us the create table statement for your table(s) and that will give us a better idea of how to help you out.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    13
    Rep Power
    0
    I will look into data normalizing.

    The convert statement were added by dbStudio when I imported them from access, If I remove them, dbStudio puts them back.

    I have no CREATE TABLE statements, data was imported from access.
  16. #9
  17. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    I suggest you take a look at following syntax:
    SHOW CREATE TABLE - http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html
    DESCRIBE - http://dev.mysql.com/doc/refman/5.0/en/describe.html
    EXPLAIN - http://dev.mysql.com/doc/refman/5.0/en/explain.html


    It can help you to display how the table is created (SHOW and DESCRIBE) and how MySQL is using you query to get the result (EXPLAIN)
  18. #10
  19. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    Originally Posted by tbarkdull
    I have no CREATE TABLE statements, data was imported from access.
    If you don't have those you don't have tables. see the post above for how to show the tables.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    13
    Rep Power
    0
    Let me clarify, I Used the wizard included in dbSudio to import all the data and the view (queries)
  22. #12
  23. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1958
    Originally Posted by tbarkdull
    Let me clarify, I Used the wizard included in dbSudio to import all the data and the view (queries)
    Have you look at the link i provided above?
    The first link have information about how you can get the CREATE TABLE statement of the table you had imported.

IMN logo majestic logo threadwatch logo seochat tools logo