SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Queries in huge table

    Hi,

    I need your help

    I have table called music, and it is 200 MB big. ( more that 140000 rows)
    in this table I have the following fields:

    ID, Songname, Artist, SUBBY, Songtext, count. All the fields are indexed except the Songtext.

    Now sometime when I display the top ten visited pages, ( I use LIMIT 10 on the query) the database start to be slow.

    My point here what is better to create 2 - 3 separate table ( one table ID, Songname, Artist, SUBBY, second one AID(same like ID) and count and third one onliy with the Songtext. and make a correlation between them

    Should this reduce loading time of my website? or should this reduce the mysql query time?
    World is too small
    http://www.spynets.com

  2. #2
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Before you go doing that, place indexes on fields that you are WHERE and ORDER BY ing. This is where you start. If you post the table structures and sample SQL that is pulling from the table, I can help you further.

  3. #3
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ID int(11) No auto_increment
    Songname varchar(50) No
    Artist varchar(50) No
    SUBBYvarchar(50) Yes NULL
    Date date Yes NULL
    Songtext blob BINARY Yes NULL
    count int(11)




    indexes:

    KeynameType Cardinality ActionField
    PRIMARY PRIMARY 115203 ID
    PRIMARY_KEY INDEX 115203 ID
    Artist INDEX None Artist
    Songname INDEX None Songname
    Count INDEX 264 count

    sql:
    SELECT Singer, COUNT(*) FROM xyz WHERE ArtistLIKE '%s%%' GROUP BY Artist


    or

    SELECT ID, Songname , Artist FROM xyz ORDER BY ID DESC LIMIT 40";


    thanks in advance for your help
    Last edited by Nune; Jun 24, 2004 at 12:42.
    World is too small
    http://www.spynets.com

  4. #4
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, if you're having performance issues on the second query, something is really wrong. On a dataset with ~115K records that query should return almost instantaneously, so if that is the case, I would look more towards bottlenecks in the server itself, or perhaps your PHP code?

    If you're not doing a whole lot of inserts (or you do them in big chunks and can afford to run an OPTIMIZE TABLE after each large insert), there's no problem in creating "covering" indexes across those fields that are heavily used in BOTH the SELECT and WHERE/ORDER BY clauses.

    I am having a bit of trouble with this query, however:
    Code:
    SELECT Singer, COUNT(*) FROM xyz WHERE ArtistLIKE '%s%%' GROUP BY Artist
    As I could not find any field called "Singer" in the table. Perhaps you meant "Artist"? If that's the case, then the LIKE expression is not going to use an index in its current form... The % before and after the "s" will preclude the index from being used, since MySQL will have to do a complete index scan. If possible, you should try to a) not use LIKE, and if you absolutely MUST use LIKE, b) put a constant in the start of the expression, (e.g. WHERE fieldX LIKE 's%'.

    If this doesn't meet your needs for these searches, I would consider using FULLTEXT indexing on those columns. You will have finer control over querying, and will have the ability to order the results based on a "match rank".

    HTH


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
  •