SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Generic question about MySQL queries

    My shared hosting account was just suspended due to excessive processing of MySQL queries. I do plan on upgrading, but first I want to know if this is normal...

    I have several queries on my site that observe numbers of records well into the millions. This is primarily because each time a submission is viewed on my site, a record is added to a 'submission_views' table. These records are counted each time the popularity of a submission is calculated.

    There are several areas of my site where many submissions are shown in order of popularity, meaning every 'submission_view' record is counted for each submission.

    Is it normal to have queries that process millions of records like this?

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes and no.

    It is normal to have many thousands of records in a table. It's also necessary to 'index' the tables properly so that unecessry table scans can be avoided.

    Make sure your tables are properly indexed and of course 'normalised' to at least '3NF'

    bazz

  3. #3
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, the submission_id in the views table is indexed.

    What did you mean by that last part though? ('normalised' to at least '3NF')

  4. #4
    SitePoint Addict
    Join Date
    Jan 2008
    Location
    Palm Harbor, FL
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the sort of thing I am talking about:

    Code MySQL:
    # Query_time: 2  Lock_time: 0  Rows_sent: 10  Rows_examined: 1604905
     
    SELECT submission_data.id, submission_data.title, submission_data.thumbnail_source, submission_data.type 
    FROM submission_data 
    LEFT JOIN (SELECT COUNT(id) AS id_count, submission_id 
    FROM submission_views WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY) 
    GROUP BY submission_id) AS popularity ON popularity.submission_id = submission_data.id 
    WHERE submission_data.status = 'complete' 
    GROUP BY submission_data.id 
    ORDER BY popularity.id_count DESC, submission_data.unique_views DESC, submission_data.submission_date DESC
    LIMIT 0, 10

    This query, which examined 1,604,905 rows, is simply a query called upon to show the overall most popular submissions.

  5. #5
    Made with a Mac! philm's Avatar
    Join Date
    Sep 2001
    Location
    Portsmouth, UK
    Posts
    735
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could probably reduce that further by adding another index on the 'submission_data' table for the 'status' column.

    Of course not knowing the whole picture it depends on how many submissions are not 'complete' and how many statuses you have.

    You should also use EXPLAIN which will further help you optimise the query. A few choice indexes or a slight re-order of the query could work wonders...

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look up 'database normalisation'. It is a process whereby your tables are structured and filled with data for max efficiency. The data should be very segmented and the query should do all the relating rather than adding columns do making easier to see relationships.

    I reckon that explanation is a much use as a bum full of boiled snow but maybe someone else can chip in to dexcribe it better. (Take a look at r937's signature. There's a wealth of excellent learning accessible from there).

    If you would like to post the SHOW CREATE TABLE we can help check if it is normalised OK.

    bazz


Bookmarks

Posting Permissions

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