need to reduce sql queries HELP please

LewC1986

Hi,

I have written a script where each site visitor has an individual unique sql entry, unfortunately at the moment my sql is seriously straining the script.

1) I create a record in the main table for each ip address, I use id as the primary key when id is a numerical bigint with autoincrement (for unique simple id's)

2) Within this record are three fields that are bigint's that relate to the id's of three distinct tables, so effectively to me this is a type of enum for those records (using these numbers for the "select * from table-name where owner='".$value."'"

3) Within these tables are data which can be changed and record chronological data of activity of a specific ip address (note I'm only using ip as an example)

4) I have to perform a select to get the id of the record in the related table that has a modified date which is closest to the current date (I do this by sorting descending date and limiting to one record) This has to happen for three tables so becomes more expensive performance wise.

What I need to know is if there is any way to make this more efficient or if this is just a bad application design (if so please comment on improving the performance of an application with these features). I basically need to have as much info about some intranet users as possible

bradgrafelman

I'm trying to envision your structure and what you're trying to do based on the text of your post, but I'm having a hard time doing so.

Could you instead post the raw structure of your DB design (e.g. either a textual representation of the columns and their types, or even just a bunch of CREATE TABLE queries) and perhaps some example SQL queries you're executing?

sneakyimp

Three queries is nothing. A dozen queries might be acceptable too. A hundred queries? That might be too many.

LewC1986

Hi,

Here is a sample table layout which is hopefully more clear

visitors_tbl [ id:bigint, ip:varchar(15), table2data:bigint, table3data:bigint table4data:bigint, ... ]

table2data [ id:bigint, visitor_id:bigint, user_id:bigint, ... ]

table3data [ id:bigint, visitor_id:bigint, ... ]

table4data [ id:bigint, visitor_id:bigint, favbook:bigint, ... ]

books_tbl [ id:bigint, name:text(100), isbn:varchar(20), author:bigint ]

obviously this is not the entire structure but a methodology of db design which I'm reasonably happy with. Because bigint take up less space than text it saves me db size and speed and certain strings and info can be referenced using just a number and everything is linked, the problem is that I would like this system to handle 1000 users a day with 1hour on site for 70% of those users and 10% of users being on all-day

sneakyimp

Propery indexes will save your server a lot of work.

Whether or not your system can handle that kind of load depends on many factors. How powerful is your server? How many records are in each table? How many pages does someone click in an hour of usage?

Also, you haven't really indicated precisely how many queries you are actually running and what these queries are. Your original post is about ordering your query results by date but you don't mention any date fields in that last post. If you are sorting the results, you should have an index on the field by which they are being sorted.

LewC1986

you are correct hope you sleep well...

I have fixed the problem now anyway hahaha, I was being silly!

The problem was that I was storing the id of the extra tables in the initial visitors table. Which lead to issues with too many queries being executed (for my liking I dont like multiple queries)
Why it happened I do not know, probably a combination of late nights with too much caffiene!
basically the records associate themselves to visitor data by recording the visitor id which references a record storing each ip, this means I only need to get the visitor number and user id (using a left join) and then modify multiple tables with a single query

the new system

visitors_tbl [ id:bigint(primary), ip:varchar(15), visits:bigint, firstvisit:timestamp, lastvisit:timestamp ]

users_tbl [ id:bigint, visits:bigint, uname:varchar(25), password:varchar(32), created:timestamp, lastvisit:timestamp, email:varchar(50) ]