SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: MySQL Speed

  1. #1
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Speed

    Hello,
    Got a little query about about MySQL.

    If I have a table like this.
    • table (id, name, category, url);
    • id is an auto increased integer.
    • name, category and url are all strings.
    • has over 100,000 rows


    My question is: If the table was in use all the time for retrieving rows 'where category = search term' would it give you the results quickly or would it take too long?

    What are your opinions and/or experiences of this?

    James

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    create an index on category and that query will be extremely efficient

    WHERE category = 'term' will use the index

    you can even select on partial words from left to right, WHERE category LIKE 'term%'

    but if you search for an embedded string, WHERE category LIKE '%term%', it will be slow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    it depends on the length of category and the size of the index. i have a table like your where i store information about print jobs. the printers all have names that are 25+ characters. when i used phpmyadmin to create an index on that column, i didn't specify a size, asuming it would use the entire column in the index.

    apparently that is not the case. when i noticed the query performed slowly if a prtername was in a group by or where clause, i dropped the index and added it back, specifying the size of the column in the index. this increased the speed IMMENSELY.

    you will probably have to do the same.

  4. #4
    SitePoint Member
    Join Date
    Aug 2005
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your comments. Helped me lots.

    James


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
  •