Need help restructuring Perl/MySql script
Currently, I have just over 1,000 users signed up to play a fantasy game. What's happening now is that their lists of what they have are timing out and when I try to view the MySql tables, it is also timing out due to the number of players (each with their own table).
The tables are:
users_table: number, name, userName,email, password,joined,level, game1,game1points, game2,game2points, game3,game3points
game1Items: number, itemNumber,costsNumber,itemName, itemTitle, itemTheme,itemDescription, itemImage
game1Costs: number, costsNumber, symbol,cost1,cost2,cost3
game2Items: number, itemNumber,costsNumber,itemName, itemTitle, itemTheme,itemDescription, itemImage
game2Costs: number, costsNumber, symbol,cost1,cost2,cost3
game3Items: number, itemNumber,costsNumber,itemName, itemTitle, itemTheme,itemDescription, itemImage
game3Costs: number, costsNumber, symbol,cost1,cost2,cost3
user_1: number, itemNumber,itemQty,itemSymbol,itemNotes
*All users follow after user_1 - but I have a near impossible time bringing up the MySql database anymore because there are so many users
*Users are linked to their table with their unique 'number' from the users table adding it to 'user_NUMBERHERE'
*The Items and Costs lists are separate and are updated separately
*They choose an item and can pick a level (indicated by a symbol) for that item (based on their points level) - the item cost is related to the item by the costNumber (since one item number can have several costs, depending on the symbol)
*When adding an item to their inventory, they need to be able to:
1) Search for an item by title (keyword in title and/or description), item #, cost and choose to look in the items database or their inventory
2) Look at the different Themes to see items grouped by category (weapons, clothing, magic, etc)
3) Reorder the items in a category by title, item # or cost
4) Click on an item to open a pop-up screen to view information about it
5) In that pop-up window, choose to add it to their inventory, filling out a form about whether they want it or are buying it, what color they want it to be, etc
6) Click on "Your Inventory" and have the same listings available for their items (by category, followed by ability to sort by title, item # or cost within each category)
7) When they click on an item in their inventory, a pop-up screen opens to give them info about their item
8) From the user-inventory-item pop-up, they can modify the item or delete it
9) Another option in their Inventory list is to print or view a report
*Listing JUST the items I have for "sale" to them in the games1,2 and 3 lists works 'ok' - but when it comes to compiling THEIR inventory, matching it with MY items AND the costs based on symbols they chose - it slows down and times out... same for reports.
*A lot of the users (and myself) are still on dial-up - so the scripts need to be more efficient
*I need a basic model in Perl that I can modify to add/remove item and user fields as necessary (since I add new games along the way) that will compile lists quickly and efficiently
*Please include a log-in routine that will work with or without cookies by matching the email address and password they use to log in or saving the information to a session file (user_sessions) if they don't want cookies - it must read the password and, if the password is 'expired' or 'disabled' give them an error message
*I should be able to back-up the MySql files easier...
*The way I have it now are several scripts (like [login to view URL], [login to view URL], [login to view URL], [login to view URL]) and one [login to view URL] file (where common subroutines are with a require statement for it in the other scripts) run everything
*I can do a rewrite of what I already have going if I get the right basic script outline to follow.
*I must own the script.