Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The query itself is so simple that I don't think there's anything you could possibly do to it to make it faster by modifying it. Still I would be curious whether using "IsNull(grouptag_id, 0) = 0" would make any difference whatsoever. I doubt it, but it might be fun to see if it shaves anything off.</p> <p>I think the real problem is that there are probably a ton of records that have an own_domain_id value of 4, and you don't have indexes for the other fields in the where clause. You can create individual indexes for them and/or if you want to create an index that's specifically tailored to this query then create one that keys on all 4 of the fields referenced.</p> <p>Some other observations:</p> <p>If it would be a possibility to change your code to deal with null values (perhaps just treat them as 0's) then you could get rid of the default values you're placing in most those fields and make them null instead. If not many fields actually have a value of 0 than this wouldn't do much, but if a lot of fields are set to 0 than this would cause the table to take up less space on disk which would translate to less time to scan the table.</p> <p>You could also partition the table either horizontally or vertically. </p> <p>Horizontally: You could take all the top1 fields and put them in a top1 table, all the top3 fields and put them in a top3 table, etc. Alternatively, you might do all the yahoo in one table and all bing in another table; or perhaps all the count fields in one table and all the search fields in another. If you find yourself usually only need one set of fields at a time then this would reduce the search time, but if you usually end up grabbing all the fields in most your queries then of course it wouldn't really help.</p> <p>Vertically: This one is probably a lot more work than it's worth, but you could split the records in your table over multiple tables and put them on multiple hard disks and query them all at the same time asynchronously. I've always wondered if Google does something along these lines.</p> <p>I also notice you're using a bigint for your id which is 8 bytes, as opposed to just an int which is only 4 bytes. If you think you will realistically be handling multiple billions of records at some point then bigint is obviously the way to go, but otherwise you could shrink your database by about a 100 megabytes which will also have the effect of making your searches marginally faster. And there's no reason you can't make it an int now and change it back to a bigint later if that becomes necessary.</p>
    singulars
    1. This table or related slice is empty.
    1. POtoo much time to execute
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBrandon Moore
    1. USBrandon Moore
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POtoo much time to execute
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COspecifically, try an index on (own_domain_id,log_date,grouptag_id,keyword_top1_count)
      singulars
      1. PO
      1. USysth
    2. COplease confirm which index need to add composit index or single index on columns (own_domain_id,log_date,grouptag_id,keyword_top1_count).
      singulars
      1. PO
      1. USmithuna kous
    3. COAlso how big do the search volume fields values get? If not over 4 billion, then shrinking them to regular unsigned ints would shave around 300mb off your database. And likewise an unsigned mediumint can go up to 16 million. If that would be enough for your count fields then reducing each of them would could cut off up to almost half a gig. Basically, when you're dealing with large numbers of records it becomes really important to use the smallest sized fields possible.
      singulars
      1. PO
      1. USBrandon Moore
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload