Results 1 to 10 of 10
  1. #1
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Going crazy trying to create or add indexes to MySQL table
    Okay, first of all, I'm using a modified version of the How to FTP and Import SAS Datafeeds script Mobilebadboy created in that thread.

    I want to be able to add indexes to the MySQL table this script creates. One of our fellow members here (thanks, bumpaw) and I have been working it over. But no matter what he or I have tried, I keep getting a sort of generic "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27", depending on what tweaking I do to the code I'm putting below, sometimes it says "line 22," sometimes "line 30."

    Here's the code I'm working with from the full script:

    Code:
    mysql_query("CREATE TABLE $temptable (ProductID int(11) NOT NULL default '0',
    Name varchar(255) NOT NULL default '',
    MerchantID varchar(50) NOT NULL default '',
    Merchant varchar(50) NOT NULL default '',
    Link text NOT NULL,
    Thumbnail text NOT NULL,
    BigImage text NOT NULL,
    Price varchar(50) NOT NULL default '',
    RetailPrice varchar(50) NOT NULL default '',
    Category varchar(50) NOT NULL default '',
    SubCategory varchar(50) NOT NULL default '',
    Description longtext NOT NULL,
    Custom1 text NOT NULL,
    Custom2 text NOT NULL,
    Custom3 text NOT NULL,
    Custom4 text NOT NULL,
    Custom5 text NOT NULL,
    LastUpdated varchar(100) NOT NULL default '',
    status varchar(50) NOT NULL default '',
    manufacturer varchar(50) NOT NULL default '',
    partNumber varchar(50) NOT NULL default '',
    merchantCategory varchar(50) NOT NULL default '',
    merchantSubcategory varchar(50) NOT NULL default '',
    shortDescription varchar(50) NOT NULL default '',
    ISBN varchar(50) NOT NULL default '',
    UPC varchar(50) NOT NULL default '',
    KEY Name (Name),
    Key Link (Link(200)),
    PRIMARY KEY (ProductID)),") or
        die(mysql_error());
    Those lines, by the way, are lines 59-88 in the script, no where near the lines further up the script that the lame error message keeps alerting me to.

    When bumpaw and I first worked this over, I thought I would want to make indexes on "Name" and "Link," hence the lines near the end. Actually, he pointed out based on the table and PHP scripts I'm using, that "Name" and "Custom1" and "Custom2" would be better.

    Anyone have a suggestion? I would be happy to post my entire script here if that would be helpful, or send it as an email attachment to anyone who would know how to help.

    Thanks!
    Generate more fake news.

  2. #2
    ABW Veteran Mr. Sal's Avatar
    Join Date
    January 18th, 2005
    Posts
    6,795
    Quote Originally Posted by writerguy
    I want to be able to add indexes to the MySQL table this script creates.

    But no matter what he or I have tried, I keep getting a sort of generic "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 27", depending on what tweaking I do to the code I'm putting below, sometimes it says "line 22," sometimes "line 30."

    ---------------------------
    KEY Name (Name),
    Key Link (Link(200)),
    PRIMARY KEY (ProductID)),") or
    die(mysql_error());
    ---------------------------


    When bumpaw and I first worked this over, I thought I would want to make indexes on "Name" and "Link," hence the lines near the end. Actually, he pointed out based on the table and PHP scripts I'm using, that "Name" and "Custom1" and "Custom2" would be better.

    Anyone have a suggestion? I would be happy to post my entire script here if that would be helpful, or send it as an email attachment to anyone who would know how to help.

    Thanks!
    I have no idea about how add indexes to the MySQL table because so far I have not had the need for it yet, so I don't know what to suggest.

    But, by looking at this line: PRIMARY KEY (ProductID)),") or die(mysql_error());

    Are you sure you need that comma there? (ProductID)),") or die


  3. #3
    Visual Artist & ABW Ambassador lostdeviant's Avatar
    Join Date
    September 7th, 2007
    Location
    Cuautitlán, Edo. de México
    Posts
    1,725
    If I put a coma after the last item in a list I WILL get an error. Mysql loves to say the error is on like 30 when it is actually a problem with line 29 missing a ; or whatever.
    try the following (no, this is not copy/paste)

    last-column-in-list, INDEX(columntoaddtoindex,anothercolumntoindex)
    )") or die .......the rest of your code

  4. #4
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Brilliant catch. Okay, probably something I SHOULD have caught myself. It was the errant comma. Thanks so much.

    Related question: The structure of the MySQL table shows the indexes were created, but under the column titled "Cardinality" when I look at the table structure, it shows "57094" for the PRIMARY keyname, and "NONE" for both of the indexes the script created.

    Is that what it's supposed to be? The table has 57,094 records in it.

    Shouldn't the "Name" and "Link" indexes I created show something besides "NONE," or is that because I haven't yet run any "SELECT" or other statements on the table?

    I guess my concern is I might not have completed the indexing by simply running this script to create the table and the indexes??

    My ignorance comes shining through. That's okay, somebody has to ask the obvious questions, right? I've made a career out of that. LOL!
    Generate more fake news.

  5. #5
    Visual Artist & ABW Ambassador lostdeviant's Avatar
    Join Date
    September 7th, 2007
    Location
    Cuautitlán, Edo. de México
    Posts
    1,725
    The name of the index (at least looking at one of my tables) is on the left under the heading "Keyname". The names of the columns included in the index are on the right under the heading "Field".

    this is assuming you are looking at the table in phpmyadmin on the structure tab, bottom of the page :-)

  6. #6
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    Quote Originally Posted by writerguy
    Shouldn't the "Name" and "Link" indexes I created show something besides "NONE," or is that because I haven't yet run any "SELECT" or other statements on the table?
    They should show the number of unique items in the table. So if there are no duplicates Names or Links they should both show 57,094.

    Try running $sql = 'REPAIR TABLE `XXXXXXX`'; etc.. after you've imported the data. Or in phpmyadmin go to the page that lists all your tables. Check the tables you want to repair and at the bottom change the "With Selected" dropdown to "Repair Table".
    Those lines, by the way, are lines 59-88 in the script, no where near the lines further up the script that the lame error message keeps alerting me to
    The line number in your error message refers to the line number of the mysql query not the php line number.

    To help debug my scripts I give each "die" a unique number, as in:
    or die("Query 2 failed : " . mysql_error()); .So I know immediately which of the sql queries failed.

    Bob

  7. #7
    Lite On The Do, Heavy On The Nuts Donuts's Avatar
    Join Date
    January 18th, 2005
    Location
    Winter Park, FL
    Posts
    6,930
    Quote Originally Posted by writerguy
    Related question: The structure of the MySQL table shows the indexes were created, but under the column titled "Cardinality" when I look at the table structure, it shows "57094" for the PRIMARY keyname, and "NONE" for both of the indexes the script created.

    Is that what it's supposed to be? The table has 57,094 records in it.
    First, go read this artilce, it'll help in several fronts:
    http://www.databasejournal.com/featu...0897_1382791_1

    Then read about cardinality here:
    http://www.mysqldba.co.uk/articles/2...s-cardinality/

    The cardinality will tell you about how unique the data stored in a particular column is - which can lead you to figure out how "indexable" it is.

    The length of your index's key is important in all of this. Think about a phone book, with tabs on it's page's ends, that take you to the last name of a person. If the tabs are 1 character in length - A, B, C, etc - then those index tabs get you to a certain size data with a certain speed. If they are two characters long - AA, AB, AC, AD, etc - they get you to a smaller chunk faster. However, when the length is increased further, like let's say to 40 characters, then their are too many tabs to look through - and while the data it brings us to, when we find the one we're looking for, is a nice small data set, combing through the zillions of 40 characters long tabs is too time consuming.

    So searches are made faster by having indexes, or tabs in a phone book, but the optimum length of the index itself, is rarely 1 or the full size of the data itself.

    Another consider is that indices must be built... imagine someone gluing tabs to the phone book pages... if you do a lot of table writing, more or longer indices will slow things down... if your tables don't change all that much, and you're querying them a lot, more indices will be helpful.

    And, Cardinality can be expressed several ways, sometimes people use it as the number of unique data counts in a colum (that's what you see in the phpmyadmin gui), while people talk about it as a ratio of the number of unique data divided by the total number of rows. So be careful about which one you're referring to.

    And learn to use tools that optimize queries and indices for you - that's where the real speed comes from.

    And finally, back to your question... your keys cardinality, at 57094, equals the number of rows of data... so you have basically glued an index tab to every row in your table... this won't speed many of your searches much at all (but that does depend on what those searches are for too).

  8. #8
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Wow. You guys are the greatest. Time I read all that stuff, I'll be an expert on db indexes.

    Uh, well, no probably not an expert. But I might at least have more of clue what I'm trying to do. Thanks so much!
    Generate more fake news.

  9. #9
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    FWIW, I've found the one-off database tasks like creating and modifying tables far easier to do with phpMyAdmin.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  10. #10
    ABW Ambassador sjangro's Avatar
    Join Date
    January 18th, 2005
    Location
    Boston
    Posts
    1,529
    Brilliant description of indexing, Pat.

    As someone who studied this stuff in college, I've always just known how it works.
    I don't know if I'd ever have been able to put it into such easy to understand terms.

  11. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Mysql Table Definition for Google Aff Network Datafeeds
    By Uncle Rico in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: December 23rd, 2009, 12:35 PM
  2. Update Mysql Table Using Inner Join Problem
    By Uncle Rico in forum Programming / Datafeeds / Tools
    Replies: 8
    Last Post: March 26th, 2009, 08:42 PM
  3. Replies: 6
    Last Post: February 20th, 2008, 08:05 AM
  4. Quotation marks from datafeed DOUBLED in MySQL table
    By Gib in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: February 25th, 2005, 05:25 PM
  5. MYSQL - Indexes and Fields
    By ahugedeal in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: January 28th, 2003, 07:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •