Results 1 to 10 of 10
August 1st, 2008, 06:34 PM #1Going 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:
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());
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.
August 1st, 2008, 08:29 PM #2Originally Posted by writerguy
But, by looking at this line: PRIMARY KEY (ProductID)),") or die(mysql_error());
Are you sure you need that comma there? (ProductID)),") or die
August 1st, 2008, 08:46 PM #3
- Join Date
- September 7th, 2007
- Cuautitlán, Edo. de México
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)
)") or die .......the rest of your code
August 1st, 2008, 09:02 PM #4
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.
August 1st, 2008, 09:53 PM #5
- Join Date
- September 7th, 2007
- Cuautitlán, Edo. de México
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 :-)
August 2nd, 2008, 05:39 AM #6
Originally Posted by writerguy
- Join Date
- October 22nd, 2006
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
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.
August 2nd, 2008, 02:26 PM #7Originally Posted by writerguy
Then read about cardinality here:
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).
August 2nd, 2008, 02:56 PM #8
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.
August 2nd, 2008, 04:39 PM #9
FWIW, I've found the one-off database tasks like creating and modifying tables far easier to do with phpMyAdmin.
August 2nd, 2008, 05:07 PM #10
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.
By Uncle Rico in forum Programming / Datafeeds / ToolsReplies: 11Last Post: December 23rd, 2009, 12:35 PM
By Uncle Rico in forum Programming / Datafeeds / ToolsReplies: 8Last Post: March 26th, 2009, 08:42 PM
By dealpigg in forum Midnight Cafe'Replies: 6Last Post: February 20th, 2008, 08:05 AM
By Gib in forum Programming / Datafeeds / ToolsReplies: 3Last Post: February 25th, 2005, 05:25 PM
By ahugedeal in forum Programming / Datafeeds / ToolsReplies: 0Last Post: January 28th, 2003, 07:07 PM